Forum Discussion

bdehning's avatar
bdehning
Icon for Post Prodigy rankPost Prodigy
3 years ago
Solved

Need help with Measure for Matrix Table and Top 5

I really need help in writing a measure.  I have attched sample PBI Table and screen shot of current table.  I use a Filter for the Reporting Location City and a Measure that shows the Top 5 Reportin...
Need Help
bdehning's avatar
bdehning
Icon for Post Prodigy rankPost Prodigy
3 years ago

 

PaulDBrown's avatar
PaulDBrown
Icon for Community Champion rankCommunity Champion
3 years ago

Yes, I see what you mean!

First of all, change the [Ref] measure to:

 

Ref =
VAR _MX =
    MAXX (
        ALL ( InjuryCause[Cause Grouping] ),
        CALCULATE ( SUM ( LossRunToExcel[Total Gross Incurred] ) )
    )
VAR _LNGTh =
    LEN ( _MX ) + 1
RETURN
    COUNT ( LossRunToExcel[Total Gross Incurred] ) * POWER ( 10, _LNGTh )
        + SUM ( LossRunToExcel[Total Gross Incurred] )

 

Create a new measure to sort the matrix by:

 

Sort measure =
IF (
    ISINSCOPE ( InjuryCause[Cause Grouping] ),
    IF (
        ISBLANK ( [Sum of Total Gross Incurred] ),
        BLANK (),
        RANKX ( ALLSELECTED ( InjuryCause[Cause Grouping] ), [Ref],, ASC, SKIP )
    ),
    [Sum of Total Gross Incurred]
)

 

Then add and sort the matrix by the [Sort measure] 

in ascending order. Finally, turn of word wrap under Values and  column headers and "hide" the 
[Sort measure] measure by dragging the right column boundary left:

 

 
  • bdehning's avatar
    bdehning
    Icon for Post Prodigy rankPost Prodigy
    3 years ago

    Maybe I did somehting wring.  Here are screen shot of Acesnd and Descend Sort Measure.  I Included my filter

     

    Ascend

     

    Decend

     

    • PaulDBrown's avatar
      PaulDBrown
      Icon for Community Champion rankCommunity Champion
      3 years ago

      You did nothing wrong...I did!

      Try with this measure to sort the matrix by:

       

      Sort Measure =
IF (
    ISINSCOPE ( InjuryCause[Cause Grouping] ),
    IF (
        ISBLANK ( [Sum of Total Gross Incurred] ),
        BLANK (),
        RANKX ( ALL ( InjuryCause[Cause Grouping] ), [Ref],, DESC, SKIP )
    ),
    RANKX (
        ALL ( LossRunToExcel[Reporting Location City] ),
        CALCULATE (
            [Sum of Total Gross Incurred],
            ALLEXCEPT ( LossRunToExcel, LossRunToExcel[Reporting Location City] )
        ),
        ,
        DESC
    )
)

       

       

      • bdehning's avatar
        bdehning
        Icon for Post Prodigy rankPost Prodigy
        3 years ago

        Paul,

         

        I got the sample file to be like yours and it looks fine.

         

        When I applied Measure to my main file I still got a city out of order.  I can provide more data later to sample file.  San Antionio is below Kansas City.   Not sure why?