Forum Discussion
RANKX depending on filter context
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
MeasuresOutput
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())))))
3 Replies
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/
hi, afraid that's not working. which tables am I meant to reference here?
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())))))
