Get rid of filter context by using ALL then bring filter context back

[khaeshr46]

Hi,

I'm looking to only see the total of Chairs in the "Tables" filter context only.

I've created a measure that gets rid of the filter conext at sub-category column.

Sales for Chairs 1 ALL at Sub-category level = CALCULATE([Total Sales],

                        'Product ID'[Sub-Category] = "Chairs")

I've attemped this to bring the filter conext back in but no go. From my theoretical understanding of DAX, Sales for Chairs 1 ALL at Sub-category level contains ALL('Product ID'[Sub-Category]) which I believe is not letting the KEEPFILTERS work in the 2nd measure.

Sales for Chairs in Tables = CALCULATE([Sales for Chairs 1 ALL at Sub-category level],KEEPFILTERS('Product ID'[Sub-Category]="Tables"))

Is this possible?

Below is my desired output for "Sales of Chairs in Tables". The 2 rows for Bookcases and Chairs should not be there.

[EylesIT · Accepted solution]

@khaeshr46, here is my suggested solution.

Create a measure called [Sales for Chairs in Tables]:

Sales for Chairs in Tables = 
    SUMX(
        VALUES(YourTable[Subcat]),
        IF(YourTable[Subcat] = "Tables", CALCULATE(SUM(YourTable[sales]), YourTable[Subcat] = "Chairs"))
    )

This gives me the following results with a subset of your data:

Hope this helps.

[khaeshr46]

Did the job! Thanks!!