Forum Discussion
Solved
DAX Formula for subtracting columns?
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!
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
3 Replies
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
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 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