Forum Discussion
Create Calculated Table with a Rank Column that Interacts with Columns in Original Source Table
My goal is to create a summary table 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've created a calculated table with a rank measure using the DAX below:
Ranking Table = SUMMARIZE('Data','Data'[Service],"Volume",SUM(Data[Volume]))Rank = RANK(DENSE, ALLSELECTED('Ranking Table'), ORDERBY('Ranking Table'[Volume], DESC), LAST)
This results in the following summary table:
| Rank | Service | Volume |
| 1 | Cancer | 72 |
| 2 | Neurologic | 67 |
| 3 | Cardiac | 66 |
| 4 | Orthopedic | 57 |
I would utlimately like the Rank column to update when filtering on columns "ZIP Code" and "Setting" 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" and "Setting = Outpatient" I would like the summary table to read as follows:
| Rank | Service | Volume |
| 1 | Neurologic | 15 |
| 2 | Cancer | 6 |
| 3 | Cardiac | 5 |
| 4 | Orthopedic | 2 |
Any help is appreciated!
pls try this
rank = rankx(ALLSELECTED('Table'[Service]),[_volumn])pls see the attachment below
4 Replies
Try not creating a calculated table, but use a measure on your source table with RANKX.
Yes you're correct - I realized I omitted a key element:
The summary ranked table should contain a measure, not a column from the data source (I couldn't use the RANK functions with a measure which is why I was trying the calculated table route). I reposted with that correction here:
pls try this
rank = rankx(ALLSELECTED('Table'[Service]),[_volumn])pls see the attachment below
Yes you're correct - I realized I omitted a key element:
The summary ranked table should contain a measure, not a column from the data source (I couldn't use the RANK functions with a measure which is why I was trying the calculated table route). I reposted with that correction here:
