Forum Discussion
Using RANKX, with a measure, and ISINSCOPE, within a matrix hierarchy
Hello -
I have tried everything I can think of or could find content for, in order to understand how to achieve the proper rankings at each level in a two-level hierarchy but I am stumped and frustrated.
Issue:
I have a Measure, Total Hours per Driver, that I am trying to use in the calculation of another Measure, Total Hours per Driver (rank). I would like the Rank Measure to provide a 1-10 ranking for Regions at the top of the hierarchy, then provide a ranking of 1-20 for the individual Drivers once I drill-down into each Region.
As it stands, I can achieve the proper ranking at the Driver level, but not the Regional level.
The current formula looks as such:
Total Hours per Driver (rank) =
VAR IsRegionVisible =
ISINSCOPE('Region Week Range Key'[Region])
VAR IsDriverVisible =
ISINSCOPE('Region Week Range Key'[Driver ID])
VAR DriverMeasure =
RANKX(ALLSELECTED('Region Week Range Key'[Driver ID]), CALCULATE([Total Hours per Driver (measure)]),,DESC)
VAR RegMeasure =
RANKX(ALLSELECTED('Region Week Range Key'[Region]), CALCULATE([Total Hours per Driver (measure)]),,DESC)
RETURN
SWITCH(TRUE(),
IsDriverVisible, DriverMeasure,
IsRegionVisible, RegMeasure,
BLANK())
There is a relationship between two tables connected by a key that looks like:
The output currently looks like:
ALL REGIONS (Top Level)
"ATLANTIC" Region (Drill-Down)
My goal would be to keep the Atlantic Region ranking as is, which is correct but adjust the Regional rankings so that they reflect 1-8 rankings.
Thank you so much for your time and assistance.
Best,
MP
You read correctly that a CALCULATE() is needed there; however, when you reference a measure like that, it is automatically wrapped in one. If you were to write SUM(Table[Column1]), that would need to be wrapped in CALCULATE().
For your measure, to troubleshoot, do you get the expected result for region rank when you take driver out of the hierarchy? Or if you comment out your return with // and return your isdrivervisible and isregionvisible variables instead, you can help diagnose where the problem is.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
6 Replies
A couple comments/questions to consider:
1. Why are you wrapping the measures inside a CALCULATE()? I don't think that is necessary and could be the problem.
2. What do you mean 1-8 vs 1-20? The rank will be based on how many rows are in the ALLSELECTED() tables. Do you mean there are 8 regions and 20 drivers, so that is what you expect?
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
1. I read in a different thread that for a Measure to function appropriately in RANKX, it needed to be wrapped in CALCULATE(), but I just removed the CALCULATE() and it provides the same result as before.
2. Sorry for the lack of clarity, you are correct. My ranking expectation is based on there being 8 Regions and each Region has on average 20 Drivers, which is where the reference to those values came from.
You read correctly that a CALCULATE() is needed there; however, when you reference a measure like that, it is automatically wrapped in one. If you were to write SUM(Table[Column1]), that would need to be wrapped in CALCULATE().
For your measure, to troubleshoot, do you get the expected result for region rank when you take driver out of the hierarchy? Or if you comment out your return with // and return your isdrivervisible and isregionvisible variables instead, you can help diagnose where the problem is.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
Hi all, Im actually facing an issue where I have a list of hierarchy.
Country>Customer>Division>Product
My measure is a switch function where if the slicer selected is sales, it will show sales amount, if cost, it will show cost amount.
However, i need to see top 20 by customer. the hierarchy should also only reflect on the top 20 customer.
for example from the top 20 customer, we will know these top 20 customer is coming from which country, what division they have what product they have..
is there a dax for this that i can refer to?
