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!

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Yep, works perfectly. Exactly what I needed as well. The requirement description is very clear and this proposed solution works well. I still don't understand why the ALL() function is not enough to remove all the filters, wether in the visual itself as well as filters in slicers and in the filters pane, but the solution works. Thanks so much.