Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Dynamic RANKX with multiple filters and slicer

Hi,   Power BI newbie here.  I am trying to dynamically rank my dataset for each Sale Transaction based on the Item Price. However i also have a slicer filter for Item Warehouse location which...
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi Anonymous 
    Yes it is working but this is a calculated column which cannot interact with the filter context.
    Try this measure on the full set of data

    Ranking Measure = 
RANKX (
    ALLSELECTED ( Sales ),
    CALCULATE ( SUM ( Sales[Item_Price] ) ),
    ,
    ASC,
    DENSE
)

tamerj1's avatar
tamerj1
Community Champion
4 years ago

Hi Anonymous 
Unless you need to use the ranking to slice your data, you can create is as a measure. 

 

Rank Measure =
RANKX ( Sales, SUM ( Sales[Item_Price] ),, ASC, DENSE )

The following calculated column will give ranking per location but does not work for multiple selection. If you select multiple locations it will still calculate the ranking seperately for each location

Ranking Column Per Warehouse =
RANKX (
    CALCULATETABLE (
        Sales,
        ALLEXCEPT ( Sales, Sales[Item_Warehouse_Location] )
    ),
    Sales[Item_Price],
    ,
    ASC,
    DENSE
)

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi tamerj1 ,

 

Thank you for your quick response.

Yes i will need the rank to be sliced in another visual so a calculated column might be preferred.

I tried your suggested output however its slightly different to what i am after. Sorry, i may not have been clear in the initial post. The rank will be based on the Item Price for each sales transaction and if i filter out a specific location then the rank needs to dynamically recalculate.

My expected 'Rank' column would be as below with all locations selected:

Sales_Transaction_IDItem_Price Item_Warehouse_LocationRank
6481.30Melbourne1
64813.95Sydney5
6489.83Brisbane3
6487.84Brisbane2
64812.10Adelaide4

 

And if i unselected Brisbane on my Item_warehouse_Location then the rank column should present as:

Sales_Transaction_IDItem_Price ( Dollars $)Item_Warehouse_LocationRank
6481.30Melbourne1
64813.95Sydney3
64812.10Adelaide2

 

Hope this is a bit clearer. Thanks for helping out!

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Anonymous 
    This dynatic behaviour cannot be achieved by a calculated column. Only measures can be that dynamic. Please check if this measure works and if yes we'll try to find a solution for the ranking slicer

    Rank Measure =
CALCULATE (
    RANKX ( Sales, SUM ( Sales[Item_Price] ),, ASC, DENSE ),
    ALLEXCEPT ( Sales, Sales[Sales_Transaction_ID] ),
    VALUES ( Sales[Item_Warehouse_Location] )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      tamerj1 
      I've implemented this measure and is currently showing all rows as 1. I also have a date slicer on the page (which i might need at a later stage ) and it looks like this ignores the date slicer and looks at the whole dataset.

      The initial measure i had below works but need it to also include slicer for Item_warehouse_location:

       

       

      Rank =
RANKX (
    FILTER (
        Sales,
        EARLIER ( Sales[Sales_Transaction_ID] ) = Sales[Sales_Transaction_ID]
    ),
    Sales[Item_Price],
    ,
    ASC,
    DENSE
)

       

       

      • tamerj1's avatar
        tamerj1
        Community Champion
        4 years ago

        Hi Anonymous 
        Yes it is working but this is a calculated column which cannot interact with the filter context.
        Try this measure on the full set of data

        Ranking Measure = 
RANKX (
    ALLSELECTED ( Sales ),
    CALCULATE ( SUM ( Sales[Item_Price] ) ),
    ,
    ASC,
    DENSE
)