Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I am preparing a dashboard in Power BI Desktop and have daily sales data of our distributors and their chemists. We have many distributors and every distributor have thousands of chemist (as their customers).
I want to analyse Distributor as well as Chemist wise performance and want to create two CALCULATED COLUMNS in Power BI for Ranking of Chemist within distributor as well as on overall on the basis of Sales made to them.
I will then use these ranks to create slicers for top 10, 20, 30, Chemists.
The sample data is attached. Please help.
https://www.dropbox.com/sh/ham67hhlgt0mcyn/AADHawUkp5zSId4Sm3CS9Zmwa?dl=0
Solved! Go to Solution.
Hi @Anonymous
I made changes of 2 of the columns formula:
Chemist Rank within Distributor = RANKX(FILTER('Table',[Distributor Code]=EARLIER('Table'[Distributor Code])),[Column],,DESC,Dense)
Overall Chemist Rank = RANKX('Table',[Column 3],,DESC,Dense)
Other 2 columns remained:
Pbix attached.
Hi @Anonymous
Please note that I can rank the table based on values, but the result is a little bit different with yours: For example, if there 1,1 exist, the next rank number will start from 3 rather than 2. coz the second place has been replaced by 1.
Added four columns:
Column = SUMX(FILTER('Table (2)',[Chemist Code]=EARLIER([Chemist Code])&&[Distributor Code]=EARLIER([Distributor Code])),'Table (2)'[Value of Sales])
Chemist Rank within Distributor = RANKX(FILTER('Table (2)',[Distributor Code]=EARLIER('Table (2)'[Distributor Code])),[Column],,DESC)
Column 3 = SUMX(FILTER('Table (2)',[Chemist Code]=EARLIER('Table (2)'[Chemist Code])),[Value of Sales])
Overall Chemist Rank = RANKX('Table (2)',[Column 3],,DESC)
Thanks for your time and the effort. Actually, the ranks are not in agreement with my sample data. If a distributor have only three chemists then obviously their ranks will be 1, 2 & 3 and same is the case in over all ranking.
Could you please devise a solution which matches my results.
Regards,
Hi @Anonymous
I made changes of 2 of the columns formula:
Chemist Rank within Distributor = RANKX(FILTER('Table',[Distributor Code]=EARLIER('Table'[Distributor Code])),[Column],,DESC,Dense)
Overall Chemist Rank = RANKX('Table',[Column 3],,DESC,Dense)
Other 2 columns remained:
Pbix attached.
User | Count |
---|---|
94 | |
78 | |
73 | |
64 | |
60 |
User | Count |
---|---|
106 | |
97 | |
76 | |
63 | |
61 |