Forum Discussion

badboy66's avatar
badboy66
New Member
7 years ago
Solved

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?

 

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

 

 

 

 

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

     

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

     

    Regards,

    Frank

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

     

    For more details, please check the pbix as attached.

     

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

     

    Regards,

    Frank

     

9 Replies

    • badboy66's avatar
      badboy66
      New Member

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

  • 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

  • v-frfei-msft's avatar
    v-frfei-msft
    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.

     

    For more details, please check the pbix as attached.

     

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

     

    Regards,

    Frank

     

     

    • badboy66's avatar
      badboy66
      New Member

      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

       

       

      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

      • v-frfei-msft's avatar
        v-frfei-msft
        Community Support

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

         

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

         

        Regards,

        Frank