Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |