Forum Discussion
RANKX depending on filter context
- 4 years ago
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())))))
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?