Forum Discussion

bdehning's avatar
bdehning
Post 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
  • PaulDBrown's avatar
    PaulDBrown
    3 years ago

    Have you tried sorting by the sum measure in descending order?

  • PaulDBrown's avatar
    PaulDBrown
    3 years ago

    I would go for:

    Ref =
     [Sum of Total Gross Incurred]  * 1000000000000 + COUNT(LossRunToExcel[Reporting Location City])

  • bdehning's avatar
    bdehning
    3 years ago

    I got the first example to work in main file. Thank you for all the help.  I will need more time this weekend to try the more creative and visual 2nd option.  

  • PaulDBrown's avatar
    PaulDBrown
    3 years ago

    Yes, it's definitely an issue in the service. You need to sort by the city field to get the axis to respect the structure:

    It actually emulates the problem we used to have in Desktop when you turned off concatenate fields: for the setting to take effect, you needed to sort the axis by the fields:

    It might be worth reporting the issue on the issues forum:

    https://community.powerbi.com/t5/Issues/idb-p/Issues 

PaulDBrown's avatar
PaulDBrown
Community 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
Post 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
    Community 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
      Post 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?  

       

       

      • bdehning's avatar
        bdehning
        Post Prodigy
        3 years ago

        Paul, I noticed the sample file screen shot from sample file is the same issue.   Glen Carbon and Shiloh are ahead of San Antonio.  So Cities are not sorting right?

         

        Can we address that sort in there as well?