Dynamic RANKX with multiple filters and slicer

[Anonymous]

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_ID	Item_Price	Item_Warehouse_Location
648	1.30	Melbourne
648	13.95	Sydney
648	9.83	Brisbane
648	7.84	Brisbane
648	12.10	Adelaide
854	23.42	Darwin
854	4.58	Sydney
854	6.89	Perth
854	14.57	Melbourne
854	2.43	Melbourne

[tamerj1]

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]

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_ID	Item_Price	Item_Warehouse_Location	Rank
648	1.30	Melbourne	1
648	13.95	Sydney	5
648	9.83	Brisbane	3
648	7.84	Brisbane	2
648	12.10	Adelaide	4

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

Sales_Transaction_ID	Item_Price ( Dollars $)	Item_Warehouse_Location	Rank
648	1.30	Melbourne	1
648	13.95	Sydney	3
648	12.10	Adelaide	2

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

[tamerj1]

@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]

@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 · Accepted solution]

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
)

[leojhernandez]

I'm trying to achieve this same concept with slicer, but instead of using a calculation, in this example is doing a sum of sales, I need to use the earliest date. How do I achieve this?