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.
Helper II 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 |
- jjrand
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!