Forum Discussion
Calculated columns for Ranking of Customers by Distributor as well as on overall sales
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.
Community SupportHi 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,
- v-diye-msft
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.