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.
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 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!