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 i want to include. I have tried creating the rank column with the below code however run into the  error message : "A TABLE OF MULTIPLE VALUES WAS SUPPLIED WHERE A SINGLE VALUE WAS EXPECTED".

 

RANKX(filter(Sales,earlier(Sales[Sales_Transaction_ID])=Sales[Sales_Transaction_ID]&& allselected(Sales[Item_Warehouse_Location])),Sales[Item_Price],,ASC,dense)

 

 

Any assistance on this would be super appreaciated. Many thanks in advance!

 

Sample dataset:

Sales_Transaction_IDItem_PriceItem_Warehouse_Location
6481.30Melbourne
64813.95Sydney
6489.83Brisbane
6487.84Brisbane
64812.10Adelaide
85423.42Darwin
8544.58Sydney
8546.89Perth
85414.57Melbourne
8542.43Melbourne
  • 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
)

6 Replies

  • 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] )
)