Forum Discussion
Dynamic RANKX with multiple filters and slicer
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 dataRanking Measure = RANKX ( ALLSELECTED ( Sales ), CALCULATE ( SUM ( 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 dataRanking Measure = RANKX ( ALLSELECTED ( Sales ), CALCULATE ( SUM ( Sales[Item_Price] ) ), , ASC, DENSE )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?
