Calculated columns for Ranking of Customers by Distributor as well as on overall sales

[Anonymous]

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

[v-diye-msft]

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)

[Anonymous]

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,

[v-diye-msft · Accepted 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.