手機站首頁散文詩歌雜文隨筆日記小小說

散文網(wǎng) » 科技 »學(xué)習(xí) » Power BI之DAX神功：第2卷第7回 RankX排名優(yōu)化與相對排名

Power BI之DAX神功：第2卷第7回 RankX排名優(yōu)化與相對排名

2021-09-18 12:55 作者:孫興華zz 0人讀過 | 我要投稿

一、排名優(yōu)化

【度量值】總銷售 = Sum('Sheet1'[銷售])?

【度量值】排名 = RANKX ( ALL ( Sheet1[商品] ), [總銷售])

【度量值】排名優(yōu)化1 = IF(HASONEVALUE('Sheet1'[商品]),[排名])

以上優(yōu)化是我們最熟悉的讓總計顯示為空：IF+HASONEVALUE組合

現(xiàn)在我們換成多張表來試試：

<1>?第一種情況：

【度量值】總銷售 = Sum('銷售表'[銷售])?

【度量值】排名 = RANKX ( ALL ('銷售表'[商品] ),[總銷售])

【度量值】排名優(yōu)化1 = IF(HASONEVALUE('銷售表'[商品]),[排名])

行標(biāo)題使用銷售表中的商品列：

行標(biāo)題使用商品表中的商品列：

為什么，使用商品表中的商品字段做行標(biāo)題時，排名就不能顯示了呢？

這是老生常談的問題：

【度量值】排名 = RANKX ( ALL ('銷售表'[商品] ),[總銷售])

你的度量值上寫著 ALL ('銷售表'[商品] )，當(dāng)你使用商品表中的商品字段篩選時，就等于在篩選排名，由于排名不能篩選，所以都顯示為1

解決辦法：修改度量值

【度量值】排名 = RANKX ( ALL ('商品表'[商品] ),[總銷售])

以后就不要再糾結(jié)，什么時候用一端表，什么時候用多端表。這是你自己需求決定的。

天熱了穿半袖，天冷了穿羽絨服。

你做分析，必先懂自己的業(yè)務(wù)，你要知道你自己在做什么。

<2>?第二種情況：

商品C沒有銷售，就不應(yīng)該參加排名

我們還使用剛才的三個度量值測試：

系統(tǒng)會將沒有銷售的都默認(rèn)是并列最后一個名次，我個人建議在數(shù)據(jù)分析前的數(shù)據(jù)清洗就搞定這些問題，如果你要留到DAX中解決。

這種情況，再增加一個條件，銷售大于0才進行排名

兩種寫法均可，AND和OR只能是兩個條件，但是&&和||可以多條件使用

詳見《DAX神功》第1卷第18回

【度量值】排名優(yōu)化2 = IF(AND( HASONEVALUE ('商品表'[商品] ), [總銷售] >0),RANKX ( ALL ('商品表'[商品] ),[總銷售]))

【度量值】排名優(yōu)化2 = IF(HASONEVALUE ('商品表'[商品] ) && [總銷售] >0,RANKX ( ALL ('商品表'[商品] ),[總銷售]))

例如：有些門店為了銷售數(shù)據(jù)好看，某商品1元進價，正常售價是1.5元，但是他以1.1元的價格銷售，那的確好賣，你要是將1元進價的商品5毛錢賣，賣得更火，你的銷售金額高了，但是毛利率很低，這恰恰反應(yīng)你的經(jīng)營水平有問題。所以我們在實際統(tǒng)計排名中，還會看毛利率，通常毛利率低于一定值就不會參加排名了（我商品賣了3萬掙了5000元，你賣了300萬沒掙錢，還倒貼房屋水電人工費，你排第1名公平嗎？）。這時就要判斷第3個條件，例如[總銷售]>0? && [毛利率]>某某%。這個某某根據(jù)自己公司實際情況而定。賣汽車的和賣菜的毛利率一定有很大差距。

二、RankX的第四和第五參數(shù)

眾所周知，RankX共有五個參數(shù)，我們通過前兩節(jié)課，理解了它前三個參數(shù)。后兩個參數(shù)沒有原理。知道怎么用就可以了。

電影《超時空救兵》有個片斷

這哥們從唐朝穿越到現(xiàn)代，餓了，想吃烤羊腿

他問飯店服務(wù)員：需要多少銀兩？

服務(wù)員說：50

他拿出一錠銀子，服務(wù)員說：我們這里不收銀子，只收人民幣

他說：請問哪里可以獲取人民幣？

服務(wù)員說：馬路對面有個取款機可以獲取。

他在取款機前排隊，前面有一位中年男子用藍牙耳機打電話，跟電話對方說你大爺?shù)?，就在這時，取款機中的錢吐出來了。

于是，他效仿前面的男子，對著取款機說：你大爺?shù)?。說了很多次，錢沒出來。

無耐之下，他讓后面的大媽先取錢，大媽一邊跳舞一邊取款

他又效仿大媽跳舞，錢還是沒有出來，他憤怒了，拍打取款機。

..................

最后，他被精神病院帶走了，臨走時，他問醫(yī)生：剛才是不是我步伐亂了？

醫(yī)生說：沒亂。他說：那為何沒有獲取到人民幣？

醫(yī)生說：機器壞了。

這個故事告訴大家，不用什么事都研究原理，取款機取錢，你插卡，輸對密碼，賬戶上有錢，自然可以取出來。不需要原理。

如果在RankX第四和第五參數(shù)上遇到問題，那可能是剛才的步伐亂了：）

笑完之后，我們回歸正題：

<1> RankX第四參數(shù)：升序與降序

第四參數(shù)是排序規(guī)則：可以是0升序或1降序，也可以是ASC升序或DESC降序，省略這個參數(shù)默認(rèn)降序。

降序：以下寫法均可

【度量值】RANKX ( ALL ('商品表'[商品] ),[總銷售])

【度量值】RANKX ( ALL ('商品表'[商品] ),[總銷售],,1)

【度量值】RANKX ( ALL ('商品表'[商品] ),[總銷售],,DESC)

升序：以下寫法均可

【度量值】RANKX ( ALL ('商品表'[商品] ),[總銷售],,0)

【度量值】RANKX ( ALL ('商品表'[商品] ),[總銷售],,ASC)

網(wǎng)民：某哈（化名） 2021.1月初的提問

問：為何降序是從大到小，升序從小到大，什么原理？

答：原理應(yīng)該是你的步伐亂了。

<2> RankX第五參數(shù)：中國排名與國際排名

在《孫興華講PowerQuery教程 M函數(shù) 第一季》第11集中，我們用M函數(shù)的方法做過詳細(xì)案例。你知道什么是中國排名？什么是國際排名？就可以了。

第五參數(shù)排序方法：Skip國際排序；Dense中國式排序。

【度量值】國際排名 = RANKX(all(Sheet2[姓名]),[總銷量],,,Skip)

【度量值】中國排名 = RANKX(all(Sheet2[姓名]),[總銷量],,,Dense)

【度量值】普通排名 = RANKX(all(Sheet2[姓名]),[總銷量])

如果你需要國際排名，不用刻意去寫第五參數(shù)，他默認(rèn)就是國際排名。你想呀，微軟是美國的，他怎么可能默認(rèn)使用中國排名呢？

至于國際排名和中國排名的解釋，我與《The Definitive Guide to DAX》理解不同：

書中大致意思：中國排名先使用all(Sheet2[姓名])去重，而國際排名在迭代中生成 all(Sheet2[姓名])

Ps:我翻譯水平有限，建議大家看中文版譯者高飛前輩的翻譯

我用數(shù)學(xué)方式理解：

都是先使用all(Sheet2[姓名])得到去重后的表，然后根據(jù)姓名聚合[總銷量]這個度量值，以降序為例，找到最大值10，不管它有幾個10，所有最大值名次為1。接下來兩種排名各有自己的方法：

中國排名：從沒有標(biāo)記名次的銷量中找到最大值，名次=上一個名次+1。

國際排名：從沒有標(biāo)記名次的銷量中找到最大值，統(tǒng)計上一個名次有幾個重復(fù)的值，例如2個，相當(dāng)于N=2，名次=上一個名次+N

三、RankX配合ALLSELECTED函數(shù)：

詳見《DAX神功》第1卷第11回基礎(chǔ)表函數(shù)之ALLSELECTED

ALLSELECTED(表)：指定表所有列內(nèi)部篩選全部取消，但外部篩選可以。

ALLSELECTED(表[列])：指定表指定列取消內(nèi)部篩選，但外部篩選可以。

我們想實現(xiàn)矩陣行標(biāo)題取消篩選，但是切片器（外部篩選）生效

例如：

【度量值】普通排名 = RANKX(ALL(Sheet2[姓名]),[總銷量])

【度量值】相對排名 = RANKX(ALLSELECTED(Sheet2[姓名]),[總銷量])

在沒有使用外部篩選器（如：切片器）時，他們效果相同

但是使用外部篩選器（如：切片器）時

我篩選張三、王五、鄧七??

【1】普通排名因為使用的是ALL(列)內(nèi)部與外部篩選器都不能篩選，所以他們各自原本名次是多少仍然是多少。所以稱之為絕對排名。

【2】但是，我選了這三個人，我想看被選擇的這三個人，他們之間的名次，就是相對排名。相對排名因為使用的是ALLSELECTED(列)內(nèi)部篩選器不能篩選，但是外部篩選器可以篩選。

舉個簡單的例子：

14億中國人財富大排隊。第1名~第14億....名，每個人都有自己的名次。

現(xiàn)在，我從14億中國人中，提取3個人。

某云、某化騰、某興華，他們?nèi)诉€要在排出一個名次。

也許在14億人中，某云第2，某化騰第5，某興華第13億9千萬

但是，如果篩選這三個人出來比：某云第1，某化騰第2，某興華第3

Ps：就像初始狀態(tài)的虛竹一樣就是個普通的小和尚，最后連“盜”三個號之后（無崖子，巫行云，李秋水），積聚200多年功力。某興華在14億中國人中只是一個平凡的人，經(jīng)過不斷學(xué)習(xí)，掌握的知識越來越多，這離不開網(wǎng)友的支持，他在你問我答中不斷成長和進步。某興華會繼續(xù)努力，為大家傳遞更多知識。