How to exclude a specific table dimension value using CALCULATE fomula

[roytap]

Hi,

I have a problem getting the DAX with the following example:

I have a source data of Product Group and Sales

I created a Product Group Filter.

I created a custom measure using the following DAX:

Total Product Group = CALCULATE(SUM('Table'[Sales]), ALL('Table'[Product Group]))

I set an Edit Interaction on the visualization to be excluded from the Product Group filter for the Total Product Group measure so that it will always display sales of all product no matter what the product filter are selected.

Question : The DAX formula will actually return all Product Group Sales, but i want to exclude Product Group E, how can i modify the DAX to exclude Product Group E? I am actually able to get the correct value for Total Product Group by using Edit Interaction and defining Page Filter. However, I am unable to get the Product Weightage as i am unable to extract a value that is filter using Edit Interaction and Page Level Filter. FYI, i am using Card as the visualisation to display these values.

Thanks.

Best Regards,

Roy 

[v-qiuyu-msft · Accepted solution]

Hi @roytap,

You can create two measures below:

Total Product Group = CALCULATE(SUM(Sheet1[Sales]), FILTER(ALL(Sheet1),Sheet1[Product Group]<>"E"))

Product weightage = DIVIDE(CALCULATE(SUM(Sheet1[Sales])),[Total Product Group])

For more information, you can see attached pbix file. 

Best Regards,
Qiuyun Yu