Forum Discussion

samblackshaw28's avatar
samblackshaw28
Helper I
9 months ago
Solved

Rank DAX formula

Hi,

 

Please can someone help me with the DAX for Rank. I have tried to look everywhere and can't find a solution to my specific issue.

 

I am making a dashboard which is used to see how our different partnerships are performing. They each generate revenue for our company and I just want a simple DAX formula which I can put on a card visual which shows their rank compared to all the other partnerships. So if they bring the most revenue, it will be 1, 2nd 2 etc.

 

I am currently using this DAX:

Rank =
RANKX(
    ALL(JV_Lookup[JV Name]),
    CALCULATE(
        SUM(JV_Revenue_Summary[Revenue]),
        JV_Revenue_Summary[Revenue Type]="Initial"))
 
Which works perfectly when i put it in a matrix and don't have any filters applied. But as soon as I select a specific partnership in a slicer the rank goes to 1, I have tried all sorts of removing filter context but nothing seems to work. Basically I can see it is working fine from the matrix but I want it to maintain the ranks shown on the matrix even when a specific partnership is selected.
 
Can anyone help and it is much appreciated as its driving me crazy !
  • Praful_Potphode's avatar
    Praful_Potphode
    9 months ago

    Hi samblackshaw28 

    try below measure:

    Rank =
RANKX(
    ALL(JV_Lookup[JV Name]), -- Removes filters on JV Name so all partnerships are ranked
    CALCULATE(
        SUM(JV_Revenue_Summary[Revenue]),
        JV_Revenue_Summary[Revenue Type] = "Initial",
        REMOVEFILTERS(JV_Lookup[JV Name]) -- Ensures revenue is calculated across all JVs
    )
)

     

    Please Give Kudos or mark it as solution once confirmed.

     

    Thanks and Regards,

    Praful

     

6 Replies

  • DataNinja777's avatar
    DataNinja777
    Super User
    9 months ago

    Hi samblackshaw28 ,

     

    You can fix the card visual to show the ranking properly by using JV Name slicer from the separate dimension table rather than from the fact table.  I've used your original formula, and slicer is from the dimension table.  

     

     

    I hope this addresses your issue. 

     

    Best regards,

     

     

     

     

    • samblackshaw28's avatar
      samblackshaw28
      Helper I
      9 months ago

      Hi,

       

      I have just noticed that when I use the JV name (dimension table) as the slicer it works as I want it to , what I am intending to use it the JV Key (dimension table) which is simply just a concactenation of the JV number and JV name which are both on the same dimension table.

       

      Do you have any ideas as to why this would prevent the slicer from working ?

      • King7son1's avatar
        King7son1
        Frequent Visitor
        9 months ago

        Do you have JV Name and JV Key in different tables?  It is hard to tell without a proper representation of the data model.  The composite key and the JV Number are not in the sample file.  You can put JV Name, JV Key, and JV Number in one dimensioin table and it should work. 

  • Praful_Potphode's avatar
    Praful_Potphode
    Super User
    9 months ago

    Hi samblackshaw28 

    try below measure:

    Rank =
RANKX(
    ALL(JV_Lookup[JV Name]), -- Removes filters on JV Name so all partnerships are ranked
    CALCULATE(
        SUM(JV_Revenue_Summary[Revenue]),
        JV_Revenue_Summary[Revenue Type] = "Initial",
        REMOVEFILTERS(JV_Lookup[JV Name]) -- Ensures revenue is calculated across all JVs
    )
)

     

    Please Give Kudos or mark it as solution once confirmed.

     

    Thanks and Regards,

    Praful

     

  • v-echaithra's avatar
    v-echaithra
    Community Support
    9 months ago

    Hi samblackshaw28 ,

    Thank you Praful_Potphode, DataNinja777   for your inputs.
    Just following up to see if the response provided was helpful in addressing the issue. if the issue still persists feel free to reach out if you need any further clarification or assistance.

    Thank you,
    Chaithra E.

  • v-echaithra's avatar
    v-echaithra
    Community Support
    9 months ago

    Hi samblackshaw28 ,

    May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.

    Thank you.