Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to Solution.
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())))))
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?
User | Count |
---|---|
52 | |
51 | |
20 | |
17 | |
16 |
User | Count |
---|---|
113 | |
46 | |
44 | |
28 | |
22 |