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

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)

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)

Re: Ranking calculation in a table

Thank you it looks working. Smiley Happy

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 158 members 1,897 guests
Please welcome our newest community members: