Forum Discussion

JonathanJohns's avatar
JonathanJohns
Icon for Helper III rankHelper III
8 years ago
Solved

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's avatar
    iamprajot
    Icon for Responsive Resident rankResponsive 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's avatar
      JonathanJohns
      Icon for Helper III rankHelper III

      All the fields are already not summarize and that gives me this result.

      • iamprajot's avatar
        iamprajot
        Icon for Responsive Resident rankResponsive 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.