cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper IV
Helper IV

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
Highlighted
Super User IV
Super User IV

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.


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




View solution in original post

4 REPLIES 4
Highlighted
Super User IV
Super User IV

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.


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




View solution in original post

Highlighted
Helper IV
Helper IV

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

Highlighted
Super User IV
Super User IV

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


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




Highlighted
Helper IV
Helper IV

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

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors