SUM of Column if selected value is present in another column

[Deckard_X42]

Hello Everyone, 

I have been fiddeling with this issue for a long time without beeing able to find a solution, maybe you can help me. 

Here it is : 

I have a fact table with Client, Product and Amount : 

Also, I have dimension tables Client and Product that are related to the Fact table : 

I want to create a measure that give me the SUM of the "Amount" for every Client that have purchased specific Products. 

For instance, if I create a slicer and select the Product 3 : 

I want this measure to return the SUM of "Amount" filtered with only Client A and Client C but for all the products. 

In this case : Client A (92+3+79+47) + Client C (46+40+79+1). 

The issue is also that my Fact table is huge and I query it via DirectQuery and I cannot duplicate it with a SUMMARIZE or a GROUPBY. 

Is there a way to do it in the calculation directly? 

Idealy, I would like also to select multiple products and have the measure return the SUM for Client that have Product 6 AND/OR Product 3 for instance. 

Thank you in advance for your help, 

[lbendlin]

Have you considered teaching your users how to use filters?

[Deckard_X42 · Accepted solution]

The issue is that the filters will filter the amount by product. 

The following measure will produce the desired output : 

Total Amount per Client if Product present =

VAR WithProduct =

VALUES( Client[Client] )

RETURN

CALCULATE(

[Total Amount],

REMOVEFILTERS( 'Product'[Product] ),

WithProduct

)