Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello - I would like help figuring out how to identify the top 5 clients for each industry and then summing these 5 clients revenues with the end goal of seeing what % of revenues these five clients comprise of the total industry revenue.
I will be displaying this in a table, where each row is a different industry (client names will not be shown). Note: There are multiple data rows for each client so revenues will need to be summed. Some clients may be in the top 5 for multiple industries.
Thank you!
Solved! Go to Solution.
@Anonymous , refer
https://www.sqlbi.com/articles/filtering-the-top-3-products-for-each-category-in-power-bi/
Also, refer subcategory Rank
For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415
Try TOPN
Top 10 City Rank = CALCULATE([Sales],TOPN(10,all(Geography[City]),[Sales],DESC),VALUES(Geography[City Id]))
Hi @Anonymous ,
Ranking by Industry =
RANKX (
FILTER (
ALL ( ClientRevenue ),
'ClientRevenue'[Industry ] = MAX ( 'ClientRevenue'[Industry ] )
),
CALCULATE ( SUM ( 'ClientRevenue'[Revenues ] ) )
)
//Calculate %
sum per Industry =
CALCULATE (
SUM ( ClientRevenue[Revenues ] ),
ALLEXCEPT ( ClientRevenue, ClientRevenue[Industry ] )
)
% measure =
CALCULATE (
SUM ( ClientRevenue[Revenues ] ),
FILTER (
ClientRevenue,
ClientRevenue[Industry ] = MAX ( ClientRevenue[Industry ] )
)
) / [sum per Industry]
top5 total % =
SUMX (
FILTER (
ALL ( ClientRevenue ),
ClientRevenue[Industry ] = MAX ( ClientRevenue[Industry ] )
&& [Ranking by Industry] <= 5
),
[% measure]
)
My visualizations look like this:
Is the result what you want? Whether you want to calculate the % of top5/total or each in top5/top5?
please upload some data samples and expected output. And please do mask sensitive data before uploading.
Best Regards,
Eyelyn Qin
@Anonymous , refer
https://www.sqlbi.com/articles/filtering-the-top-3-products-for-each-category-in-power-bi/
Also, refer subcategory Rank
For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415
Try TOPN
Top 10 City Rank = CALCULATE([Sales],TOPN(10,all(Geography[City]),[Sales],DESC),VALUES(Geography[City Id]))
This is the "Top x and other bucket" pattern. Usually you use RANKX for that, with a bit of a twist. The article here shows how.
https://www.sqlbi.com/articles/use-of-rankx-in-power-bi-measures/
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |