Filter by Sub-Totals in Matrix

[brose]

Hello Everyone - I'm having an issue with creating a filter on sub-totals in a matrix.  The current matrix has the shown values below: 

My goal is to filter on the total sum of planned hours for the next 8 calendar weeks for a specific name.  So my first thought was to create a running Sum command like below:

 

The problem I'm running into is the sum calculation is looking at the lower level customer sub-total in the matrix when doing the search.  Below is a picture of the matrix before doing any filtering on running sum total:

I then added the calcuated sum measure to my filters and put in calculated sum is less than 100.  My expectation here is the name Lisa would be removed from the matrix becuase her total is 252, but that's not what happened.  It only removed the column for company 2 for Lisa.  

Is there a way to have a lower level like company in the matrix but filter by the total of name which is the higher level?  The goal of this report is to show all resources for the next week that have under a specific number of planned hours.  Any help would be very much appreciated!!! 

[v-lionel-msft]

Hi @brose ，

You have a matrix, like this:

And what you want is like this, right?

But when you filtered the measure you created, you got this, right?

And the reason why that happens is because your formula returns something like this:

So, you can modify your DAX like this:

Measure = 
CALCULATE(
    SUM([Planned Hours]),
    ALLEXCEPT(
        Sheet6,
        Sheet6[Name]
    )
)

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[brose]

Lionel - Thank you for the response.  When I put in the calculation (Seen below and then filter by Available is less then 40) for the next 8 calendar weeks I get 

If I take the filter away you will see all of those other weeks populate. 

What I want to happen is for the next 8 weeks show me anyone who has less than 100 hours and only Bryan should show up in the list, Lisa and Robb Should fall off.  Let me know if this doens't make any sense.  

[v-lionel-msft]

Hi @brose ,

"What I want to happen is for the next 8 weeks show me anyone who has less than 100 hours and only Bryan should show up in the list, Lisa and Robb Should fall off.  "
Do you mean you want to filter by sub-total, such as "sum of Bryan" , "sum of Lisa", "sum of Robb"?

which table does your each column come from?
And what's the relationship between these tables?

Please give me a sample data model.

Best regards,
Lionel Chen

[brose]

Correct I want the "sum of Bryan" , "sum of Lisa", "sum of Robb"? and then be able to filter by quantity.  Example.  Anyone with more than 100 hours planned over the next 3 calendar weeks don't show up in the view.  Below are the fields I'm using.  Tables are Person (Import)

Planned Hours

Begin Date 

[brose]

Any ideas on this one? @v-lionel-msft 

[brose]

 One thing to add, this is how the tables and values are structured..