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