Forum Discussion
Create Summary Ranked Table Containing a Measure that Connects to Original Source Table
Hello,
Someone very smart I know kindly provided the solution to this.
I see that gmsamborn's solution also works, feel free to choose whichever most appeals to you. You may also accept multiple solutions.
First, we have to define these two measures
IN Ranking =RANKX(ALLSELECTED(Data[Service]),[Inpatient Mix %])OP Ranking =RANKX(ALLSELECTED(Data[Service]),[Outpatient Mix %])Then we define the composite value measureComposite value = 0.25*[IN Ranking] + 0.75*[OP Ranking]Finally, we define the composite rankComposite Rank =IF(ISINSCOPE(Data[Service]),RANKX(ALLSELECTED(Data[Service]), [Composite value]))The IF ISINSCOPE part just get rids of the rank at the total level.As you can see below, both my "Composite Rank" and gmsamborn's "My Rank" work just fine, also allowing for changing the ZIP slicer. If new Service values get added, the measure should dynamically respond with the correct values.
Thanks for the response. I'm ultimately wanting to rank several metrics (in addition to Inpatient Mix %) and do a composite score by service.
Specifically, I believe I would need a way to return a Rank Value = 1 for Cancer (being able to rank in relation to the other services and with ZIP Code filtering - the rank value 1 would be filtering for ZIP Code 10001 in this example). I then would be adding that rank to the other metric rankings for Cancer to get a composite ranking.
Sorry, hard to explain without being able to attach a file, hopefully this makes sense.
Which other metrics would you like to rank by? Can you provide some more sample data, with the columns you wish to be included in the calculation, and the expected result you would like to achieve?
For example, if I added the metric Outpatient Mix % to the dataset above:
Outpatient Mix % = DIVIDE(CALCULATE(SUM('Data'[Volume]), 'Data'[Setting] IN { "Outpatient" }), SUM('Data'[Volume]))
And added a corresponding rank measure:
OP Ranking = RANKX(ALL(Data[Service]), [Outpatient Mix %])
And filtered for ZIP Code 10001 resulting in the following table:
Rank Service Outpatient Mix % 1 Neurologic 71.4% 2 Cardiac 38.5% 3 Orthopedic 33.3% 4 Cancer 23.1% I would like to be able to add the rank values for Inpatient and Outpatient Mix for Cancer together for a composite rank (1 + 4 = 5)
I don't think I'm getting the full picture. How many metrics do you have? How are you calculating the composite ranking? If you are adding 1 and 4, then dividing by 2, you get 2.5. In fact, you get 2.5 for all of services, regardless of ZIP Code chosen. I can try to help, but I need more context.
The model I have applies a weight to Inpatient vs. Outpatient. Assuming we apply a weight of 25% to Inpatient and 75% to Outpatient, here are the the Composite Calculation and Composite Rank I'd like returned when filtering on ZIP 10001.
For example, the Composite Calculation for Cancer would be (4*0.75 + 1*0.25 = 3.25). Ranked vs. other services the Composite Rank would be 4.
Service Inpatient Mix % IP Rank Outpatient Mix % OP Rank Composite Calculation Composite Rank Cancer 76.9% 1 23.1% 4 3.25 4 Cardiac 61.5% 3 38.5% 2 2.25 2 Neurologic 28.6% 4 71.4% 1 1.75 1 Orthopedic 66.7% 2 33.3% 3 2.75 3