Forum Discussion
Filtering in Matrix report based on overall subtotal value
I am trying to create a monthly report to show people who have under recorded their hours.
So far I've managed to extract and subtotal the hours but can find no way of filtering the report to show only those with a low monthly total.
I'd like to simply filter out any that have a Total value greater than 140
I've seen on epost but that seems to filter the contributing values rather than on the person's overall subtotal
Thanks for any suggestions or explanation of what to do
Fred
You should be able to create a measure, Measure = SUM([Hours]) and use that as a visual level filter. See Page 15, Table25 of attached.
4 Replies
You should be able to create a measure, Measure = SUM([Hours]) and use that as a visual level filter. See Page 15, Table25 of attached.
Thanks Greg, obvious when you look at it.
Initially it didn't work but I realised that it was filtering on the numbers that made up each month's subtotal (the report had multi-levels under the Employee, showing Project number, Name and Task name. When I removed these the filter kicked in.
I
Is it possible to retain these fields and still filter on the overall subtotal ?.
The image above was with the filter set to 140, but the overall subtotal is 187.5, so I wouldn't expect to see Adam in the filtered view.
Regards
Fred
That seems like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907