DAX: How To Sum Multiple Groupings of Absolute Values

[Anonymous]

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

[MattAllington]

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. 

[Anonymous]

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 · Accepted solution]

Use ABS only once, after subtracting the two sums

...

CALCULATE(

                          ABS( SUM('auditModelMessageCount'[Axeda ABC]) - SUM('auditModelMessageCount'[Raw Message History]) )

) ...

[Anonymous]

THANK YOU! THAT WORKS!

Test1 = SUMX(
                  VALUES('auditModelMessageCount'[auditDate]),
                            CALCULATE(

                                     ABS(

                                          SUM('auditModelMessageCount'[Axeda ABC]) - SUM('auditModelMessageCount'[Raw Message History])

                                          )

                                              )
                        )

[MattAllington]

Yeah, I just copied the OP formula and assumed there was some reason it was that way.  Obviously not 🙂