Create Summary Ranked Table Containing a Measure that Connects to Original Source Table

[patshannon11]

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!

@ryan_mayu 

@CoreyP 

[gmsamborn]

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

patshannon11.pbix

[jjrand]

Hello,

Please see if this works

Rank = RANKX(ALL(Data[Service]), [Inpatient Mix %])

[patshannon11]

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.

[jjrand]

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?

[patshannon11]

For example, if I added the metric Outpatient Mix % to the dataset above:

Outpatient Mix % = DIVIDE(CALCULATE(SUM('Data'[Volume]), 'Data'[Setting] IN { "Outpatient" }), SUM('Data'[Volume]))

And added a corresponding rank measure:

OP Ranking = RANKX(ALL(Data[Service]), [Outpatient Mix %])

And filtered for ZIP Code 10001 resulting in the following table:

Rank	Service	Outpatient Mix %
1	Neurologic	71.4%
2	Cardiac	38.5%
3	Orthopedic	33.3%
4	Cancer	23.1%

I would like to be able to add the rank values  for Inpatient and Outpatient Mix for Cancer together for a composite rank (1 + 4 = 5)

[jjrand]

I 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.

[patshannon11]

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 · Accepted solution]

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 measure

Composite value = 0.25*[IN Ranking] + 0.75*[OP Ranking]

Finally, we define the composite rank

Composite 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.

[patshannon11]

Thanks!