cancel
Showing results for
Did you mean:
Frequent Visitor

## Problem with rankx function

Dear all,

I've been thinking for hours now on the following problems but I couldn't come to a solution so far:

Ranking is done by the following formulas:

2_1_Test Ranking_UR_Test = RANKX( ALLSELECTED( Gesamtwerte) ; [1_1_1 UR final_Test] )

2_2_2 Ranking_UR increase final_Test = rankx(ALLSELECTED(Gesamtwerte);[1_2 increase UR_Test])

First problem with 2_1_Test Ranking_UR_Test: the 2 negative values are ranked with 32, 33 but they should be 4, 5

Second problem with 2_2_2 Ranking_UR increase final_Test: I couldn't find a solution for a constant growing ranking (1,2,3,4,...). Either all rows are ranked with 1 or they are ranked in a complete nonsense way (as shown on the screenshot)

Do you know what's wrong?

1_1_1 UR final is a measure to calculate the return on sales =>  1_1_1 UR final_Test = calculate(divide(sum(Gesamtwerte[betriebsergebnis]);sum(Gesamtwerte[umsatz]);blank());filter(Gesamtwerte;(Jahr_neu_Test[select value test]=Gesamtwerte[Jahr])))

6_comparison_UR VJ final is exactly the same measure calculating the value of 2 years back => 6_comparison_UR VJ final_Test = calculate(divide(sum(Gesamtwerte[betriebsergebnis]);sum(Gesamtwerte[umsatz]);blank());filter(Gesamtwerte;(Jahr_neu_Test[select value test]-Gesamtwerte[Jahr])=2))

1_2 increase UR is a measure calculating a relationship between the previous 2 measures =>1_2 increase UR_Test = CALCULATE(DIVIDE([1_1_1 UR final_Test]-[6_comparison_UR VJ final_Test];[6_comparison_UR VJ final_Test];blank()))

The filter "Jahr" in the visualisation is a measure of a complete independet table (there is no relationship between the two tables!!!)

measure => select value test = if(HASONEVALUE(Jahr_neu_Test[Jahr]);values(Jahr_neu_Test[Jahr]);blank())

2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team

## Re: Problem with rankx function

Here I update the formula as below.

`2_1_Test Ranking_UR_Test 2 = IF(IF(ISBLANK(Gesamtwerte[1_1_1 UR final_Test]),BLANK(),RANKX(ALL(Gesamtwerte),Gesamtwerte[1_1_1 UR final_Test],,ASC,Dense))<>BLANK(),RANKX(ALL(Gesamtwerte),IF(ISBLANK(Gesamtwerte[1_1_1 UR final_Test]),BLANK(),RANKX(ALL(Gesamtwerte),Gesamtwerte[1_1_1 UR final_Test],,ASC,Dense))))`

For the measure 2_2_2 ranking_UR increase final_Test, based on my test. I cannot achieve your goal here.

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Community Support Team

## Re: Problem with rankx function

Based on my test, we can create a calculated table here. And create relationship between Gesamtwerte and the new table based on ID.

`Table 2 = SUMMARIZE(Gesamtwerte,Gesamtwerte[ID],Gesamtwerte[Name])`

Then we can create a measure to get the rank correctly.

`new rank = IF(ISBLANK([1_2 increase UR_Test]),BLANK(),RANKX(ALL('Table 2'),[1_2 increase UR_Test],,DESC,Dense))`

For more details, please check the pbix as attached.

https://www.dropbox.com/s/08yu8jmaaod8hnn/Problem%20with%20rankx%20function.pbix?dl=0

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
9 REPLIES 9
Super User

## Re: Problem with rankx function

Can you post your data in a form that can be copied and pasted?

Proud to be a Datanaut!

Frequent Visitor

## Re: Problem with rankx function

I updated my post above and will upload the test data set separately

Frequent Visitor

## Re: Problem with rankx function

 ID betriebsergebnis umsatz Jahr Name 1 100 1000 2013 a 2 200 1100 2013 b 3 300 1200 2013 c 4 400 1300 2013 d 5 500 1400 2013 e 6 600 1500 2013 f 7 700 1600 2013 g 1 800 1700 2014 a 2 900 1800 2014 b 3 1000 1900 2014 c 4 1100 2000 2014 d 5 1200 2100 2014 e 6 1300 2200 2014 f 7 1400 2300 2014 g 1 1500 2400 2015 a 2 1600 2500 2015 b 3 1700 1600 2015 c 4 1800 1700 2015 d 5 1900 1800 2015 e 6 2000 1900 2015 f 7 2100 2000 2015 g 1 2200 2100 2016 a 2 2300 2200 2016 b 3 2400 2300 2016 c 4 2500 2400 2016 d 5 2600 2500 2016 e 6 2700 2600 2016 f 7 -2800 2700 2016 g 2 2900 2800 2017 b 3 -3000 2900 2017 c 5 3100 3000 2017 e 6 -2500 3100 2017 f 7 2700 3200 2017 g

Sheet Name = Gesamtwerte

Community Support Team

## Re: Problem with rankx function

Based on my test, you can update your formula of 2_1_Test Ranking_UR_Test like this, then you can get the result as you excepted.

`2_1_Test Ranking_UR_Test = RANKX( ALLSELECTED( Gesamtwerte) , [1_1_1 UR final_Test],,ASC,Dense )`

An enumeration that defines how to determine ranking when there are ties. You can get more details about tiles from the online document.

For more details, please check the pbix as attached.

https://www.dropbox.com/s/08yu8jmaaod8hnn/Problem%20with%20rankx%20function.pbix?dl=0

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: Problem with rankx function

Hi Frank,

Now it looks like that

The column 2_1_Test Ranking_UR_Test should show the blanks in column 1_1_1 UR final_Test as position 6 --> the two negative values should be ranked as 4 and 5

The ranking in column 2_2_2 ranking_UR increase final_Test still doesn't work

Community Support Team

## Re: Problem with rankx function

Here I update the formula as below.

`2_1_Test Ranking_UR_Test 2 = IF(IF(ISBLANK(Gesamtwerte[1_1_1 UR final_Test]),BLANK(),RANKX(ALL(Gesamtwerte),Gesamtwerte[1_1_1 UR final_Test],,ASC,Dense))<>BLANK(),RANKX(ALL(Gesamtwerte),IF(ISBLANK(Gesamtwerte[1_1_1 UR final_Test]),BLANK(),RANKX(ALL(Gesamtwerte),Gesamtwerte[1_1_1 UR final_Test],,ASC,Dense))))`

For the measure 2_2_2 ranking_UR increase final_Test, based on my test. I cannot achieve your goal here.

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: Problem with rankx function

Thank you for your contribution. It really helps me. Your formula works in the data set.

Is somebody able to solve the issue with the last ranking column? maybe @Greg_Deckler?

Kind regards

Highlighted
Community Support Team

## Re: Problem with rankx function

Based on my test, we can create a calculated table here. And create relationship between Gesamtwerte and the new table based on ID.

`Table 2 = SUMMARIZE(Gesamtwerte,Gesamtwerte[ID],Gesamtwerte[Name])`

Then we can create a measure to get the rank correctly.

`new rank = IF(ISBLANK([1_2 increase UR_Test]),BLANK(),RANKX(ALL('Table 2'),[1_2 increase UR_Test],,DESC,Dense))`

For more details, please check the pbix as attached.

https://www.dropbox.com/s/08yu8jmaaod8hnn/Problem%20with%20rankx%20function.pbix?dl=0

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team