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.
I am able to get the TOP N customer sales as an above table with following command
new top =
VAR RankingDimension = VALUES(Customers[Customer Name])
return
CALCULATE([Total Sales],
FILTER(RankingDimension,RANKX(ALL(Customers[Customer Name]),[Total Sales],,DESC)<=[selected N]))
but when I use it with month or product name I get the following table
Total is shown is here correct but values are not matching. Please help me with this
Thank you in advance
Solved! Go to Solution.
@sekinod when you put month or product in the rows, then the second parameter of RANKX, [total sales], will bring in these filter context into the calculation. so in each row it calculates topn in that month or product.
new top =
VAR RankingDimension = VALUES(Customers[Customer Name])
return
CALCULATE([Total Sales],
FILTER(RankingDimension,RANKX(ALL(Customers[Customer Name]),VAR c=Customers[Customer Name] RETURN CALCULATE([Total Sales],ALLSELECT(),Customers[Customer Name]=c),,DESC)<=[selected N]))
@sekinod when you put month or product in the rows, then the second parameter of RANKX, [total sales], will bring in these filter context into the calculation. so in each row it calculates topn in that month or product.
new top =
VAR RankingDimension = VALUES(Customers[Customer Name])
return
CALCULATE([Total Sales],
FILTER(RankingDimension,RANKX(ALL(Customers[Customer Name]),VAR c=Customers[Customer Name] RETURN CALCULATE([Total Sales],ALLSELECT(),Customers[Customer Name]=c),,DESC)<=[selected N]))
Much appreiciated for your help.
If it is not trouble could you please explain your code.
@sekinod depending on the data what you show, i think you want RANKX only considers the Customer Name, ingoring whatever other filter context. so, i used a allselect() to remove all the filters in the visual, then used Customers[Customer Name]=c to reapply the filter on Customer Name.
Not sure but try using VALUES in your RANKX instead of ALL (or re-use your rankingdimension variable there).
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
User | Count |
---|---|
52 | |
46 | |
20 | |
16 | |
13 |
User | Count |
---|---|
108 | |
54 | |
31 | |
20 | |
18 |