Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JonathanJohns
Helper III
Helper III

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

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

6 REPLIES 6
iamprajot
Responsive Resident
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.

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

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.

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)

AliceW
Impactful Individual
Impactful Individual

It works! Thank you SO MUCH!!

Thank you it looks working. 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.