cancel
Showing results for
Did you mean:
Regular Visitor

## 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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Filtering in Matrix report based on overall subtotal value

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.

Proud to be a Datanaut!

4 REPLIES 4
Super User

## Re: Filtering in Matrix report based on overall subtotal value

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.

Proud to be a Datanaut!

Regular Visitor

## Re: Filtering in Matrix report based on overall subtotal value

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

Super User

## Re: Filtering in Matrix report based on overall subtotal value

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

Proud to be a Datanaut!

Regular Visitor

## Re: Filtering in Matrix report based on overall subtotal value

Yes, these were the pages I found when first trying to resolve the problem, but couldn't get them to work, probably down to a lack of understanding of the DAX code and what it was doing, mainly the "__table" and "__value" parts.

Can you give me som einsight as to what they relate to please ?

Cheers

Fred