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.  

[bdehning]

You are correct, I had to turn Concatenate off for the X-Axis and then sort by the Sort Measure to ge the Desktop file to look right.  Unfortunately it loses that when published.   I will report.     

[bdehning]

Paul, here is a screen shot of an account after publishing that has less then 5 locations.  Why no line or division between Cities?

[PaulDBrown]

It looks like you have lost the option of not concatenating the fields (same issue as we discussed previously)

[bdehning]

I did report the issue and gave several screen shots of differnces between Desktop and Publishing of Clustered Column Chart with Concatenate Off.      

[bdehning]

I tried stacked column chart and same bad results

[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]

Did my post with 2 examples of the clustered column chart disappear?

[bdehning]

Now whats funny it that the Table gets different cities than the visual in my main file.    I may need to take your new visual measures and apply them to the table.     

[bdehning]

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]

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

[bdehning]

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]

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]

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]

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

[PaulDBrown]

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

[PaulDBrown]

Great!

"How could I apply the Sort Measure to the Clustered Column Chart to mirror the Matrix Table?"

Add the [Sort measure] as a tooltip to the visual and then sort the x-axis by the measure in descending order.

[PaulDBrown]

Oh, right!! I see what you mean! My mistake. The sorting measure is by incurred only; it ignores frequency!

We need to do the same calculation of frequency * PWR(10, n) + the CALCULATE() in the second RANKX of the sorting measure. I'll sort it out when I'm back at work tomorrow 

[bdehning]

Playing around with the different Dax, but now I can not seem to show the TOP Cause Grouping in Descending Count and Sum in the right order.  5 selected for Count are correct but Sum is not in Descending order when counts are equal.  

.   

[PaulDBrown]

You just need to sort the matrix by the [Incurred] measure in descending order. The [Top 5 by frequency and incurred] measure is only relevant to filter the matrix for the top 5. Once it's filtered, just sort by the [Incurred] measure

[PaulDBrown]

Juts perhaps for the sake of practicing DAX, a more elegant way to calculate meausre [Ref] would be to calculate an appropiate factor instead of the random 1000000000000. 

So:

Ref =
VAR _MX =
    MAXX (
        ALL ( InjuryCause[Cause Grouping] ),
        CALCULATE ( COUNT ( LossRunToExcel[Total Gross Incurred] ) )
    )
VAR _LNGTh =
    LEN ( _MX ) + 1
RETURN
    COUNT ( LossRunToExcel[Reporting Location City] ) * POWER ( 10, _LNGTH ) + [Sum of Total Gross Incurred]

[bdehning]

Paul,

I tried your latest Ref as a way to pratice DAX,  but it did not select the correct Top 5.