topic Re: RANKX depending on filter context in DAX Commands and Tips

RANKX depending on filter context

Tonio_EG — Thu, 03 Feb 2022 12:18:01 GMT

Hi all, hours later I still haven't been able to figure out how to show only the top2 values by rank depending on the filter context. I can get it to work for PeerGroup set to All or I can get it to work for ONE PeerGroup selected but not for both scenarios. It's driving my absolutely nuts. Any ideas???

Using Powerpivot, not Desktop app

Re: RANKX depending on filter context

amitchandak — Thu, 03 Feb 2022 12:31:44 GMT

Tonio_EG , You can Try TOPN

example
Top 3 Rank = CALCULATE([Total Qty],TOPN(3,all(Table[fruit]),[Total Qty],DESC),VALUES(Table[fruit]))

or refer this

https://www.sqlbi.com/articles/filtering-the-top-3-products-for-each-category-in-power-bi/

Re: RANKX depending on filter context

Tonio_EG — Thu, 03 Feb 2022 13:33:15 GMT

hi, afraid that's not working. which tables am I meant to reference here?

Re: RANKX depending on filter context

Tonio_EG — Mon, 07 Feb 2022 07:25:39 GMT

Here's what I ended up doing. amitchandak TOPN was the soultion after all, thanks.

SalesVolume:=var Top5 =

TOPN(5,SUMMARIZE(

ALLSELECTED(Producers),

Producers[HoldingCo],

"TopSalesAmt",

[SalesAmt]

),[TopSalesAmt])

var Subtotals = NOT(HASONEVALUE(Producers[HoldingCo])

var TopRank = IF(HASONEVALUE('Top'[Group]) && VALUES('Top'[Group])="Top 5",1,0)

var Top5Sum= SUMX(Top5,[SalesAmt])

var Top5Total = IF(NOT(HASONEVALUE(Producers[HoldingCo])) && HASONEVALUE('Top'[Group]) && TopRank = 1,TRUE(),FALSE())

var OtherTotal = IF(NOT(HASONEVALUE(Producers[HoldingCo])) && HASONEVALUE('Top'[Group]) && TopRank = 0,TRUE(),FALSE())

return

IF(NOT(HASONEVALUE('Top'[Group])),[SalesAmt],

IF(Subtotals=TRUE(),

IF(TopRank=0,CALCULATE([SalesAmt],ALL('Top'[Group]))-Top5Sum,Top5Sum),

IF(Subtotals=FALSE(),

IF(TopRank=0,IF(NOT(CONTAINS(Top5,Producers[HoldingCo],VALUES(Producers[HoldingCo]))),[SalesAmt],BLANK()),

IF(CONTAINS(Top5,Producers[HoldingCo],VALUES(Producers[HoldingCo])),[SalesAmt],BLANK())))))