topic Re: SUM of Column if selected value is present in another column in DAX Commands and Tips

SUM of Column if selected value is present in another column

Deckard_X42 — Thu, 21 Oct 2021 16:30:56 GMT

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,

Re: SUM of Column if selected value is present in another column

lbendlin — Mon, 25 Oct 2021 02:37:38 GMT

Have you considered teaching your users how to use filters?

Re: SUM of Column if selected value is present in another column

Deckard_X42 — Thu, 25 Nov 2021 10:20:34 GMT

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

)