Forum Discussion
Create Summary Ranked Table Containing a Measure that Connects to Original Source Table
My goal is to create a summary table containing a measure with a rank column that will update when filtering on different columns from the source data table. I'm starting with a source data table similar to this:
| ZIP Code | Setting | Service | Volume |
| 10001 | Inpatient | Cancer | 15 |
| 10002 | Inpatient | Cancer | 8 |
| 10003 | Inpatient | Cancer | 2 |
| 10004 | Inpatient | Cancer | 5 |
| 10001 | Outpatient | Cancer | 6 |
| 10002 | Outpatient | Cancer | 13 |
| 10003 | Outpatient | Cancer | 2 |
| 10004 | Outpatient | Cancer | 7 |
| 10001 | Inpatient | Cancer | 5 |
| 10002 | Inpatient | Cancer | 9 |
| 10003 | Inpatient | Cardiac | 18 |
| 10004 | Inpatient | Cardiac | 4 |
| 10001 | Outpatient | Cardiac | 5 |
| 10002 | Outpatient | Cardiac | 2 |
| 10003 | Outpatient | Cardiac | 1 |
| 10004 | Outpatient | Cardiac | 5 |
| 10001 | Inpatient | Cardiac | 8 |
| 10002 | Inpatient | Cardiac | 4 |
| 10003 | Inpatient | Cardiac | 6 |
| 10004 | Inpatient | Cardiac | 13 |
| 10001 | Outpatient | Neurologic | 14 |
| 10002 | Outpatient | Neurologic | 5 |
| 10003 | Outpatient | Neurologic | 8 |
| 10004 | Outpatient | Neurologic | 5 |
| 10001 | Inpatient | Neurologic | 6 |
| 10002 | Inpatient | Neurologic | 11 |
| 10003 | Inpatient | Neurologic | 3 |
| 10004 | Inpatient | Neurologic | 9 |
| 10001 | Outpatient | Neurologic | 1 |
| 10002 | Outpatient | Neurologic | 5 |
| 10003 | Outpatient | Orthopedic | 6 |
| 10004 | Outpatient | Orthopedic | 5 |
| 10001 | Inpatient | Orthopedic | 4 |
| 10002 | Inpatient | Orthopedic | 1 |
| 10003 | Inpatient | Orthopedic | 12 |
| 10004 | Inpatient | Orthopedic | 3 |
| 10001 | Outpatient | Orthopedic | 2 |
| 10002 | Outpatient | Orthopedic | 1 |
| 10003 | Outpatient | Orthopedic | 5 |
| 10004 | Outpatient | Orthopedic | 18 |
I then created a measure for Inpatient Mix % using the DAX below:
Inpatient Mix % =
DIVIDE(CALCULATE(SUM('Data'[Volume]), 'Data'[Setting] IN { "Inpatient" }), SUM('Data'[Volume]))
I then created a calculated table with a rank measure using the DAX below:
Ranking Table = SUMMARIZE('Data','Data'[Service],"Inpatient Mix %",DIVIDE(CALCULATE(SUM('Data'[Volume]), 'Data'[Setting] IN { "Inpatient" }), SUM('Data'[Volume])))Rank = RANK(DENSE, ALLSELECTED('Ranking Table'), ORDERBY('Ranking Table'[Inpatient Mix %], DESC), LAST)
This results in the following summary table:
| Rank | Service | Inpatient Mix % |
| 1 | Cardiac | 80.3% |
| 2 | Cancer | 61.1% |
| 3 | Neurologic | 43.3% |
| 4 | Orthopedic | 35.1% |
I would utlimately like the Rank column to update when filtering on the column "ZIP Code" in the original source table, but I'm having trouble finding out how to create that relationship. For example, If I filter on "ZIP Code = 10001" I would like the summary table to read as follows:
| Rank | Service | Inpatient Mix % |
| 1 | Cancer | 76.9% |
| 2 | Cardiac | 61.5% |
| 3 | Neurologic | 28.6% |
| 4 | Orthopedic | 66.7% |
Any help is appreciated!
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.
9 Replies
Hello,
Please see if this works
Rank = RANKX(ALL(Data[Service]), [Inpatient Mix %])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?
Hi patshannon11
Would something like this help?
My Rank = VAR _Table = SUMMARIZE ( ALLSELECTED( 'Data' ), 'Data'[Service], "Inpatient Mix %", DIVIDE ( CALCULATE ( SUM ( 'Data'[Volume] ), 'Data'[Setting] IN { "Inpatient" } ), SUM ( 'Data'[Volume] ) ) ) VAR _Rank = RANK ( DENSE, _Table, ORDERBY ( [Inpatient Mix %], DESC ) ) RETURN _Rank
