Forum Discussion
DAX: How To Sum Multiple Groupings of Absolute Values
| Date | Val1 | Val2 | Difference | Absolute Value Difference |
| 1/1/2018 | 0 | 6 | -6 | 6 |
| 1/2/2018 | 6 | 5 | 1 | 1 |
| 1/3/2018 | 7 | 2 | 5 | 5 |
| Total | 18 | 13 | 0 | 12 |
How do I write a DAX Formula to get 12 (The Absolute Value Difference SUMMED BY DAYS) as my desired output instead of 0?
This is the calculation and result I am getting currently:
ABS(Sum(Val1)) - ABS(Sum(Val2)) = 0
Use ABS only once, after subtracting the two sums
...
CALCULATE(
ABS( SUM('auditModelMessageCount'[Axeda ABC]) - SUM('auditModelMessageCount'[Raw Message History]) )
) ...
5 Replies
- MattAllington
Community Champion
It depends on your table structure and you have t said if you have one or more tables. Assuming one table, this should work
SUMX(VALUES(table[Date]),CALCULATE(abs(sum(val1))-abs(sum(val2))))
you can learn about sum vs sumx in power BI at this link.
Matt,
Thanks for the quick response. My result is still 0 with that formula (see below in context). To answer question about data structure.... all my columns are from 1 table like you correctly assummed from the example above.
Test1 = SUMX(
VALUES('auditModelMessageCount'[auditDate]),
CALCULATE(
ABS(SUM('auditModelMessageCount'[Axeda ABC])) - ABS(SUM('auditModelMessageCount'[Raw Message History]))
)
)
- dedelman_clng
Use ABS only once, after subtracting the two sums
...
CALCULATE(
ABS( SUM('auditModelMessageCount'[Axeda ABC]) - SUM('auditModelMessageCount'[Raw Message History]) )
) ...
