cancel
Showing results for
Did you mean:
JonathanJohns Member

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.

1 ACCEPTED SOLUTION

Accepted Solutions
iamprajot Established Member

Re: Ranking calculation in a table

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)

5 REPLIES 5
iamprajot Established Member

Re: Ranking calculation in a table

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 Member

Re: Ranking calculation in a table

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

iamprajot Established Member

Re: Ranking calculation in a table

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.

iamprajot Established Member

Re: Ranking calculation in a table

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)

JonathanJohns Member

Re: Ranking calculation in a table

Thank you it looks working. Announcements Top Kudoed Authors
Users Online
Currently online: 158 members 1,897 guests
Recent signins:
• tmendoza • divjoy690 • Regiso • Mr_E • dianeos • Mbaig1683 • Helen1969 • Mbaig1683 • Helen1969 • RyanDOwens • MattTeemer • johanyow • siwilson20 • tiagoprata 