Calculate measure for each group while using measure filter

[holypond2001]

Hello, I need your help urgently!

I want to create a total measure KPI card that matches to the total value of another table visual.

The table visual is displaying at the higher level product number hierarchy (PRODUCT_NO), so all the measures and filters are automatically calculated at that grouped level. The highlighted 5067871 is the correct value I want.

Below is my new dax which isn't returning the total value I want.

TEST = CALCULATE([12M Excess ($)],ALLEXCEPT(Dim_Product,Dim_Product[PRODUCT_NO]),FILTER(Dim_Product,[12M Receipts & Sales]="Yes"))

To give more contexts of my filter, it's summing 12 month receipt & sales amounts and if they are both >=0 mark as "Yes", and I'm filtering "Yes" for the calculation.

This is summed at PRODUCT_NO level in the table visual, but expanded to index_key level in my new dax, thus returning different number than the table total of [12M Excess ($)] measure. 

Adding the index_key to the table will match the totals but that's not the value I want to show.

I've read somewhere that ALLEXCEPT() is nullified if used together with FILTER() function. But in order to use the measure as a filter condition, I couldn't find any other option than to use the FILTER() function.

How do I make it evaluate the measures at the PRODUCT_NO level while using the filter?

Thank you so much for your help in advance!!!

[Anonymous]

Hi, @holypond2001 

Given that you'll need to add up 12 months' receipts and sales amounts, and filter where both are marked as "yes," and then calculate at that level, you may need to adjust your approach a bit:

TEST = 
CALCULATE(
    [12M Excess ($)],
    FILTER(
        SUMMARIZE(Dim_Product, Dim_Product[PRODUCT_NO], "FilteredMeasure", [12M Receipts & Sales]),
        [FilteredMeasure] = "Yes"
    )
)

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data)

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

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