cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted

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 :

 

Sans titre.png

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
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)

View solution in original post

5 REPLIES 5
iamprajot Established Member
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.

Re: Ranking calculation in a table

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

iamprajot Established Member
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
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)

View solution in original post

Re: Ranking calculation in a table

Thank you it looks working. Smiley Happy

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 53 members 1,174 guests
Please welcome our newest community members: