cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MitieFred Regular Visitor
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.

Time Report.PNG

 

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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


4 REPLIES 4
Super User
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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


MitieFred Regular Visitor
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.

Filter.PNGI

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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


MitieFred Regular Visitor
Regular Visitor

Re: Filtering in Matrix report based on overall subtotal value

Hi @Greg_Deckler ,

 

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