Forum Discussion
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 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
Have you tried sorting by the sum measure in descending order?
I would go for:
Ref =
[Sum of Total Gross Incurred] * 1000000000000 + COUNT(LossRunToExcel[Reporting Location City])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.
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:
49 Replies
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
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?Paul,
I enjoyed the work from back in 2022 and how you solved the TOP 5 and Top 5 Issue>
I use the following
This goes in Values and and we sort Reporting Location City by Top 5 of the Sort Measure.Sort measure Count Location Cause Sum =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('LossRun'[Reporting Location City])), "text"))VAR _Pre = _FreqByCity *POWER(10, _LN*2)VAR _Inc = CALCULATE(RANKX(ALLSELECTED(LossRun[Reporting Location City]), [Sum of Total Gross Incurred],,ASC,Dense), ALLSELECTED(InjuryCause[Cause Grouping]))VAR _Mid = _Inc * POWER(10, _LN)RETURNIF(ISBLANK([Count of Total Gross Incurred]), BLANK(), _Pre + _Mid + RANKX(ALLSELECTED(InjuryCause[Cause Grouping]),[Ref],,ASC,Skip))
Ref =VAR _MX =MAXX (ALL ( InjuryCause[Cause Grouping] ),CALCULATE ( SUM ( LossRun[Total Gross Incurred] ) ))VAR _LNGTh =LEN ( FORMAT ( INT ( _MX ), "Text" ) ) + 1RETURNCOUNT ( LossRun[Total Gross Incurred] ) * POWER ( 20, _LNGTh )+ SUM ( LossRun[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 ))
Top 5 by frequency and Sevreity is used as filter is less than 6 to give Top 5 Cause Grouping.This worked as we wanted but I tried swapping out Reporting Location City in the Sort Measure with Day of Week for another Table and Visual but it does work as it does not break ties. Day of Week is Text Column as well. Any ides why it would not work like Reporting Locatioj City?
Paul,
Are you still out there? I need a tweak on 1 or two of the measures as I occasionally get a broken X Axis Sort on Columns and Table Sort Measure Count Totals are the same and Ties still occur?
Have you tried sorting by the sum measure in descending order?
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]I would go for:
Ref =
[Sum of Total Gross Incurred] * 1000000000000 + COUNT(LossRunToExcel[Reporting Location City])
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]Paul,
I tried your latest Ref as a way to pratice DAX, but it did not select the correct Top 5.
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.
.
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
I can sort descening incurred no problem, but then Reporting Location City Freqency Counts are not decending?
Can you post a depiction?
Paul, it does the City just fine but is it possible to rank the Cause Grouping the same as ther table? Down from highest to lowest?
Paul, I found one account so far where sort is not working. Look under San Jose and 9 for NC is under the two 8's.? I can try more later.
Paul,
I changed
POWER ( 20, _LNGTh ) from 10 and that worked on example I set. Should I set Power even higher just to make sure?