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 diff...
  • 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

     

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.