Create Calculated Table with a Rank Column that Interacts with Columns in Original Source Table

[patshannon11]

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!

[ryan_mayu · Accepted solution]

@patshannon11 

pls try this

rank = rankx(ALLSELECTED('Table'[Service]),[_volumn])

pls see the attachment below

[patshannon11]

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:

https://community.fabric.microsoft.com/t5/Desktop/Create-Summary-Ranked-Table-Containing-a-Measure-t...

[CoreyP]

Try not creating a calculated table, but use a measure on your source table with RANKX.

[patshannon11]

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:

https://community.fabric.microsoft.com/t5/Desktop/Create-Summary-Ranked-Table-Containing-a-Measure-t...