Forum Discussion

brose's avatar
brose
Regular Visitor
6 years ago

Filter by Sub-Totals in Matrix

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 h...
Need Help
v-lionel-msft's avatar
v-lionel-msft
Community Support
6 years ago

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's avatar
    brose
    Regular Visitor
    6 years ago

    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's avatar
      v-lionel-msft
      Community Support
      6 years ago

      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's avatar
        brose
        Regular Visitor
        6 years ago

        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