Need help with Measure for Matrix Table and Top 5

[bdehning]

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 Reporting Location Cities by a Count and then Sum.   That part works great.    I have been trying to get help but not getting results on my own.   Is this really not as simple as it seems.   

Now I need someone to write a measure that I could add to the filter that would keep the Cause Grouping under each City to the Top 5 by Frequency.      If a tie, then first look at top Frequency and then use the Incurred to break the tie to keep only 5. 

From image below , for example, under Kansas City there are three 3's which leaves 6 total causes  The tied 3's are Assault, Exposure and NC. 

The measure if working, would keep

Cut, Puncture 19

Burn, Scald 5

STF 5

NC 3

Assault 3

San Antonio would keep

Cut, Puncture 12

STF 10

Caught Between 2

Assault 2

Burn, Scald 2

Sample PBI File 

[PaulDBrown · Accepted solution]

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

[PaulDBrown · Accepted solution]

I would go for:

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

[bdehning · Accepted solution]

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 · Accepted solution]

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

[bdehning]

Yes Thank you so much.  

How  would you tweak the two measures If I have a page where Sum is looked at first and tie breaker would be count?

Top 5 by Frequency and Incurred measure would be easy to swap out Sum of Total for Count of Total but what abour Ref? 

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

[PaulDBrown · Accepted solution]

I would go for:

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

[bdehning]

Thanks again for the work.   

[PaulDBrown]

Try the following:

two measures

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

Top 5 by frequency and Incurred =
IF (
    ISBLANK ( [Sum of Total Gross Incurred] ),
    BLANK (),
    RANKX ( ALL ( InjuryCause[Cause Grouping] ), [Ref],, DESC, SKIP )
)

And then set the filter for this [Top 5 by frequency and Incurred] to less than 6 in the filter pane

[bdehning]

Paul,  So far looks pretty good.  I really appreciate the work.  

I need to cross check several accounts.

Is there a way by sorting or tweaking the measure so that Cause Grouping in Matrix will show the highest Sum if Count is tied.  When I applied measures to main file sometime ties at count of 1 or 2 are showing Lower Sum values over higher Sum Values?