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
badboy66
New Member

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:

 

Picture 1.PNG

 

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?

 

Many thanks in advance!

 

 

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

 

Picture 2.PNGPicture 3.PNGPicture 4.PNG

 

 

 

2 ACCEPTED SOLUTIONS

Hi @badboy66,

 

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

Capture.PNG

 

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 others find it more quickly.

View solution in original post

Hi @badboy66,

 

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

12333.PNG

 

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 others find it more quickly.

View solution in original post

9 REPLIES 9
v-frfei-msft
Community Support
Community Support

Hi @badboy66,

 

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.

 

Capture.PNG

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 others find it more quickly.

Hi Frank,

 

Thank you for your reply

Sry, at the moment I don't have access to dropbox

 

I applied the changes mentioned in your reply above

 

Now it looks like that

 

Picture 5.PNG

 

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

Hi @badboy66,

 

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

Capture.PNG

 

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 others find it more quickly.

Hi @v-frfei-msft,

 

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

Hi @badboy66,

 

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

12333.PNG

 

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 others find it more quickly.

Hi @badboy66,

 

Does that make sense? If any other question ,feel free to let me know.

 

Rgeards,

Frank

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

IDbetriebsergebnisumsatzJahrName
110010002013a
220011002013b
330012002013c
440013002013d
550014002013e
660015002013f
770016002013g
180017002014a
290018002014b
3100019002014c
4110020002014d
5120021002014e
6130022002014f
7140023002014g
1150024002015a
2160025002015b
3170016002015c
4180017002015d
5190018002015e
6200019002015f
7210020002015g
1220021002016a
2230022002016b
3240023002016c
4250024002016d
5260025002016e
6270026002016f
7-280027002016g
2290028002017b
3-300029002017c
5310030002017e
6-250031002017f
7270032002017g

 

Sheet Name = Gesamtwerte

Greg_Deckler
Super User
Super User

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

 

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

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.