Forum Discussion
Ranking calculation in a table
Good morning,
I have a problem with a calculation. I want to do a PARETO Chart. I used the tutorial at this address : https://powerbi.tips/2016/10/pareto-charting/
But when I do it, it doesn't work beacause some of my Sum Sales are equal and the measures don't work. I let you see on this picture :
As you can see, the last calculations are wrong because the ranking is the same for the two last lines. I give you the formula I used :
Ranking = RANKX( 'Summary', 'Summary'[Sum Sales])
Cumulative Total = CALCULATE( SUM( Summary[Sum Sales] ), FILTER( ALLSELECTED( Summary ), Summary[Ranking] <= MAX( Summary[Ranking] ) ))
Total Sales = CALCULATE( SUM( Summary[Sum Sales] ) , ALLSELECTED( Summary ) )
Cumulative Percent = [Cumulative Total] / [Total Sales]
Do you have any idea do correct my calculations please ?
Thank you for your help.
Create an additional column that is different from the similar Sales Column
SupportCol = Data[Sales]+(RANDBETWEEN(1,100)/10000000)
This will add decimal value to the similar numbers hence making it non similar.
Then do the ranking based on this SupportCol
RANKX(TableName,[SupportCol],,DESC)
6 Replies
- iamprajot
Responsive Resident
Calculations seems fine just do one thing,
In the table properties, choose Don't Summarize for Weeks & Ranking.
It's grouping them hence similar figures.
- JonathanJohns
Helper III
All the fields are already not summarize and that gives me this result.
- iamprajot
Responsive Resident
The problem is the ranking, it is giving same Ranks for two similar figures.
I have tried to find it out but couldn't succed in getting a good solution for Ranking of same figures.