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.

     

gmsamborn's avatar
gmsamborn
Super User
2 years ago

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