DAX Formula for subtracting columns?

[TheITAnalyst13]

Hi All,

I'm trying to subtract the value of 2 columns in such a way as below:

Column A	Column B	Column C	Column D
A	0	8	SUM(B2:B3)-SUM(C2:C3)
A	3	0
C	0	10	SUM(B4:B5)-SUM(C4:C5)
C	5	0

Can someone please help to give me a formula for Column D? I tried the formula below but it is not working:

Column D = CALCULATE(SUM(Column B)-SUM(Column C))

Appreciate help on this!

[v-yulgu-msft]

Hi @TheITAnalyst13,

Column D =
CALCULATE ( SUM ( Tb1[Column B] ), ALLEXCEPT ( Tb1, Tb1[Column A] ) )
    - CALCULATE ( SUM ( Tb1[Column C] ), ALLEXCEPT ( Tb1, Tb1[Column A] ) )

Best regards,

Yuliana Gu

[TheITAnalyst13]

Thanks for the suggestion Yuliana!

Is there a way I can sum the same item together so it will only appear in one row.

From this:

Column A	Column B	Column C	Column D
A	0	8	SUM(B2:B3)-SUM(C2:C3)
A	3	0
C	0	10	SUM(B4:B5)-SUM(C4:C5)
C	5	0

To this:

Column A	Column B	Column C	Column D
A	0+3	8+0	B9-C9
C	0+5	10+0	B10-C10

[v-yulgu-msft · Accepted solution]

Hi @TheITAnalyst13,

That case, you could new a calculated table with below DAX formula:

Tb2 =
SUMMARIZE (
    Tb1,
    Tb1[Column A],
    "ColumnB", SUM ( Tb1[Column B] ),
    "ColumnC", SUM ( Tb1[Column C] ),
    "ColumnD", SUM ( Tb1[Column B] ) - SUM ( Tb1[Column C] )
)

Best regards,

Yuliana Gu