Forum Discussion

patshannon11's avatar
patshannon11
Frequent Visitor
2 years ago
Solved

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 si...
Need Help
  • jjrand's avatar
    jjrand
    2 years ago

    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's avatar
patshannon11
Frequent Visitor
2 years ago

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:

RankServiceOutpatient Mix %
1Neurologic71.4%
2Cardiac38.5%
3Orthopedic33.3%
4Cancer23.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's avatar
jjrand
Icon for Helper I rankHelper I
2 years ago

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.