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)-SU...
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
v-yulgu-msft
Microsoft Employee
Microsoft EmployeeHi 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 |
- v-yulgu-msft
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