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

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

 

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.