Define a measure subtracting two sums depending on one column value

amaaiia — Wed, 08 May 2024 08:18:09 GMT

I have a table with the following data:

group	field1	field2	field3
A	20	3	8
A	16	2	5
B	10	5	5
B	15	4	2
A	7	9	1

I need to define a measure that performs the subtraction of sum(field1)-sum(field3) when group='A' and sum(field2)-sum(field3) when group='B'. How can I achieve this?

Something in pseudocode like:

sum(if group='A' then field1 else field2)-sum(field3)

With the previous table, the result should be:

(20+16+5+4+7)-(8+5+5+2+1)=52-21=31

Re: Define a measure subtracting two sums depending on one column value

Jihwan_Kim — Tue, 07 May 2024 15:48:25 GMT

Hi,

Please check the below picture and the attached pbix file.

Expected result measure: = VAR _conditionA = FILTER ( data, data[group] = "A" ) VAR _conditionB = FILTER ( data, data[group] = "B" ) RETURN SUMX ( _conditionA, data[field1] - data[field3] ) + SUMX ( _conditionB, data[field2] - data[field3] )

topic Define a measure subtracting two sums depending on one column value in DAX Commands and Tips

Define a measure subtracting two sums depending on one column value

Re: Define a measure subtracting two sums depending on one column value