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 

bdehning's avatar
bdehning
Post Prodigy
3 years ago

I can sort descening incurred no problem, but then Reporting Location City Freqency Counts are not decending?

PaulDBrown's avatar
PaulDBrown
Community Champion
3 years ago

Can you post a depiction?

  • bdehning's avatar
    bdehning
    Post Prodigy
    3 years ago

     

    • 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