Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Fixed versus Dynamic Rank with RankX

I have a basic matrix table with Product Name, Profit and two rank columns (1 fixed, 1 dynamic) that are calculated with the following measures:   (1) Product Profit Rank = RANKX(ALLSELECTED(Orders...
  • Anonymous's avatar
    Anonymous
    6 years ago
    // This is a global rank that
// does not respond to
// any filters.

[Product Profit Fixed Rank] = 
    RANKX(
        ALL( Orders[Product Name] )
        CALCULATE(
            SUM( Orders[Profit] ),
            ALLEXCEPT(
                Orders,
                Orders[Product Name]
            )
        )
    )
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

Anonymous , you rank all product[name] will be dynamic inside any group by/axis/legend you will use. This how it works. Anything else you add to visual, will create a group for Rank

 

example

City Rank = RANKX(all(Geography[City]),[Sales]) // this gives me rank 1only when I use with City ID
Geography Rank = RANKX(all(Geography),[Sales]) // correct Rank with city id as both city and city Id at lowest level
City and ID Rank = RANKX(all(Geography[City],Geography[City Id]),[Sales]) // correct Rank with city id as both city and city Id are part of the Rank.

 

See if this can help you a bit.

 

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    amitchandak 

    Sorry, I dont follow what you are trying to say. Isn't the point of using ALL so that the expression will ignore filter context? 

     

    ALL - "Returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied. This function is useful for clearing filters and creating calculations on all the rows in a table."

     

    So if I want to create a universal rank that stays the same is there a way to adjust my current formula to do that?

    https://drive.google.com/file/d/1eqGAh4aieAVV9LgsM1heHP62Ey65Usu4/view?usp=sharing

     

    Product Profit Fixed Rank = RANKX(ALL(Orders[Product Name]),CALCULATE(SUM(Orders[Profit])))
     
    Thanks
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago
      // This is a global rank that
// does not respond to
// any filters.

[Product Profit Fixed Rank] = 
    RANKX(
        ALL( Orders[Product Name] )
        CALCULATE(
            SUM( Orders[Profit] ),
            ALLEXCEPT(
                Orders,
                Orders[Product Name]
            )
        )
    )
      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Amazing. Exactly what I was looking for. Thanks!