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.