Use a filter in a measure dax

[Phobos1]

Hello, I need some help with a DAX statement for a measure. I created the below statement for a matrix table and it works fine to display the the subtotals of several categories in %. It shows the subtotal per category added up to 100%, these categories each have several items.

PercSubtotal = DIVIDE (SUM( 'Workflow’[Pages] ),

CALCULATE( SUM( 'Workflow'[Pages]), ALLEXCEPT( 'Workflow', 'Workflow'[Projects] )

 ))

So this was fine, until I had to use a filter on the matrix table as a whole. Then the subtotals did not add up to 100% anymore, showing the subtotal % without the filtered out data (see screenshot below). So now, the measure would need to be filtered as well in order for the subtotals to show 100% again, but how can that be done?

[Phobos1]

Anyone?

[Anonymous · Accepted solution]

Hi @Phobos1 ,

I suggest you to add the column which want to filter in ALLEXCEPT function. You don't need to add that column into your visual.

My Sample:

Here I add a Region in my table. When I use your code, I will meet the problem like yours. If I filter Region, I won't get 100% in my visual, and percentage won't be dynamic by this column.

We need to add [Region] in ALLEXCEPT.

PercSubtotal =
DIVIDE (
    SUM ( 'Workflow'[Pages] ),
    CALCULATE (
        SUM ( 'Workflow'[Pages] ),
        ALLEXCEPT ( 'Workflow', 'Workflow'[Projects], Workflow[Region] )
    )
)

Result is as below.

Best Regards,
Rico Zhou

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

[Phobos1]

Hi Rico, this works perfectly, simple and elegant. Thanks so much for your help!

[amitchandak]

@Phobos1 , We need create a measure two remove the filter of row, say task

PercSubtotal = DIVIDE (SUM( 'Workflow’[Pages] ),

CALCULATE( SUM( 'Workflow'[Pages]), ALLEXCEPT( 'Workflow', 'Workflow'[Projects] ), removefilter('Workflow'[Task])

))

or

PercSubtotal = DIVIDE (SUM( 'Workflow’[Pages] ),

CALCULATE( SUM( 'Workflow'[Pages]),  removefilter('Workflow'[Task])

))

You should get subtotal in each row, then you can use visual level filter

[Phobos1]

Thanks for the quick reply, I tried altering the measure by adding the 'removefilters', but then I get an error message:

Parameter is not the correct type.

And also: The ALLEXCEPT function expects a table reference for argument '3', but a table expression was used.