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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
MV13
Frequent Visitor

RankX Measure ties affecting Pareto graph.

Hi All

I am currently trying to build a Pareto graph that dynamically changes based on filter selections. I have created 3 measures to do this.

The first measure Ranks the Data as Follows.

 

Rank = IF(HASONEVALUE('Table'[Group]),
    RANKX(ALLSELECTED('Table'[Group]),
    CALCULATE(SUM('Table'[Duration]), ALLSELECTED('DateTable')),,DESC))

 

 

The second measure calculates the cumulative Sum of the Duration based on  the rank as follows

 

 

Cumulative  Duration = VAR XY=[Rank]
RETURN( CALCULATE(
	SUM('Table'[Duration]),
	FILTER(ALLSELECTED('Table'[Booking Group] ),
		[Rank]<= XY
	)))

I then built a pareto based on these by [Cumulative Duration]/[Total Duration]

 

 

This method works fine until theres a point in the data where two Groups have the same Duration. This results in a flat line(As shown below) in the Pareto graph since the Durations ar the same and the duration is not cumulative in this instance.

Flat lined paretoFlat lined pareto

Can anyone help with breaking these ties to ensure that the pareto graph works as it should.

 

Thanks in advance

1 ACCEPTED SOLUTION
MV13
Frequent Visitor

Update: I have managed to solve the case of ties. To do this, I created a unique ID column in my data and then added the (Minimum of the ID)/Large Number to the rank calculation. So if two items are ranked 5 and one item had a Minimum ID of 2 and the second one had a Minimum of 4 I then took the ID and divided by 100 to get 0.02 and 0.04. So the ranks can be broken. The table below explains this:

 

ValueRankMin of IDMin ID/100New ValueNew rank
100130.03100.031
12290.0912.092
11350.0511.053
10470.0710.075
10480.0810.084
7510.017.016

 

So the New rank would then be:

Rank = IF(HASONEVALUE('Table'[Group]),
    RANKX(ALLSELECTED('Table'[Group]),
    CALCULATE(SUM('Table'[Duration]), ALLSELECTED('DateTable'))+INT(CALCULATE(MIN('Table'[ID])))/100,,DESC))

 

Hope this logic is clear for future queries.

 

This solution was derived from a method shown on the site below:

http://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures

View solution in original post

2 REPLIES 2
MV13
Frequent Visitor

Update: I have managed to solve the case of ties. To do this, I created a unique ID column in my data and then added the (Minimum of the ID)/Large Number to the rank calculation. So if two items are ranked 5 and one item had a Minimum ID of 2 and the second one had a Minimum of 4 I then took the ID and divided by 100 to get 0.02 and 0.04. So the ranks can be broken. The table below explains this:

 

ValueRankMin of IDMin ID/100New ValueNew rank
100130.03100.031
12290.0912.092
11350.0511.053
10470.0710.075
10480.0810.084
7510.017.016

 

So the New rank would then be:

Rank = IF(HASONEVALUE('Table'[Group]),
    RANKX(ALLSELECTED('Table'[Group]),
    CALCULATE(SUM('Table'[Duration]), ALLSELECTED('DateTable'))+INT(CALCULATE(MIN('Table'[ID])))/100,,DESC))

 

Hope this logic is clear for future queries.

 

This solution was derived from a method shown on the site below:

http://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures

v-huizhn-msft
Microsoft Employee
Microsoft Employee

Hi @MV13,

Please create another measure to test using the formula below, I create another measure named "Test of Rank", it should returen the max value utill current rows. Please add the [Rank] and [Test of Rank] measure in a table to compare the result.

 

Test of rank=VAR XY=[Rank]
RETURN(YY)


If the result is right, while the duration is still not cumulative, please share your .pbix file for further analysis.

Best Regards,
Angelia

Helpful resources

Announcements
Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors