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

See table and chart.  Shiloh is wrong on table and Frisco is right on Visual.    Frisco is 28 and Shlioh is 27   I mentioned there was an issue with the table yesterday for some accounts.   

 

bdehning's avatar
bdehning
Post Prodigy
3 years ago

Ok now I think I am good.  I applied fhe new Sum Measure from chart to table.  

 

 

  • bdehning's avatar
    bdehning
    Post Prodigy
    3 years ago

    Here is what Chart look like when published?  

     

    Not sure why it looks good before publihing and not after?   Why is it doing that?

     

  • bdehning's avatar
    bdehning
    Post Prodigy
    3 years ago

    How would we remove the City Name from the Axis and still keep sorting  by City and Cause Grouping for it to publish right?

     

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    3 years ago

    If you remove the city field, the measures loose the field context, so the visual will display aggredate values for case grouping

  • bdehning's avatar
    bdehning
    Post Prodigy
    3 years ago

    Paul, how would you tweak the following you provided to make page where Sum is used first and Count is as Tie breaker.  I tried to replace Count with Sum for all and got close.  

     

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

     

     

    Sort measure Count Location Cause =
    VAR _FreqByCity = CALCULATE([Count of Total Gross Incurred], FILTER(ALL(InjuryCause[Cause Grouping]), [Top 5 by frequency and Incurred]<6))
    VAR _LN = LEN(FORMAT(CALCULATE([Count of Total Gross Incurred], ALL('LossRunToExcel'[Reporting Location City])), "text"))
    VAR _Pre = _FreqByCity *POWER(10, _LN*2)
    VAR _Inc = CALCULATE(RANKX(ALLSELECTED(LossRunToExcel[Reporting Location City]), [Sum of Total Gross Incurred],,ASC,Dense), ALLSELECTED(InjuryCause[Cause Grouping]))
    VAR _Mid = _Inc * POWER(10, _LN)
    RETURN
    IF(ISBLANK([Count of Total Gross Incurred]), BLANK(), _Pre + _Mid + RANKX(ALLSELECTED(InjuryCause[Cause Grouping]),[Ref],,ASC,Skip))
     
    Top 5 by frequency and Incurred =
    IF (
        ISBLANK ( [Sum of Total Gross Incurred] ),
        BLANK (),
        RANKX ( ALL ( InjuryCause[Cause Grouping] ), [Ref],, DESC, SKIP )
  • bdehning's avatar
    bdehning
    Post Prodigy
    3 years ago

    I believe I got it work.  I had to recheck all.