Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Have you tried sorting by the sum measure in descending order?
Proud to be a Super User!
Paul on Linkedin.
I would go for:
Ref =
[Sum of Total Gross Incurred] * 1000000000000 + COUNT(LossRunToExcel[Reporting Location City])
Proud to be a Super User!
Paul on Linkedin.
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:
https://community.powerbi.com/t5/Issues/idb-p/Issues
Proud to be a Super User!
Paul on Linkedin.
Have you tried sorting by the sum measure in descending order?
Proud to be a Super User!
Paul on Linkedin.
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])
Proud to be a Super User!
Paul on Linkedin.
Thanks again for the work.
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
Proud to be a Super User!
Paul on Linkedin.
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
118 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |