Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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
)
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
)
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!
@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] )
)
@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
)
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
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
76 | |
53 | |
46 | |
16 | |
12 |