Absolute value per row on filtered column

[simber]

I have this table:

Time	Forecast	Value	Customer
2022-01-01 00:00:00	5	8	Customer1
2022-01-01 01:00:00	4	7	Customer1
2022-01-01 02:00:00	6	8	Customer1
2022-01-01 00:00:00	10	12	Customer2
2022-01-01 01:00:00	11	14	Customer2
2022-01-01 02:00:00	15	13	Customer2

How do I create a measure or calculation so I can sum my forecast column and my value column , calculate the difference (forecast-value) and then calculate the absolute value of this per row?

So If I create a matrix in report view and put Time in row and this new calculation in Values, I should get a matrix that looks like this:

Time	Absolute Difference
2022-01-01 00:00:00	5
2022-01-01 01:00:00	6
2022-01-01 02:00:00	0

And If I sum this table for the year 2022 i get:

Time	Absolute Difference
2022	11

[v-rongtiep-msft]

Hi @simber ,

I have create a simple sample, please refer to my pbix file to see if it helps you.

Measure = ( MAX('Table'[Forecast])-MAX('Table'[Value]))

Measure 2 = ABS(SUMX(ALL('Table'),[Measure]))

Measure 3 = ABS(SUMX(FILTER(ALL('Table'),'Table'[Time]=SELECTEDVALUE('Table'[Time])),[Measure]))

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[simber]

I tried to use your code but it didn't exactly do what I intended it to do. I want the forecast to first be aggregated for each customer and area. Then aggregate the value column the same way. After this is done I want to calculate the absolute difference between these columns. It is important to be able to filter these out. the actual table more looks like this: 

Time	Customer	Area	Asset	Forecast	Value
2022-01-01 00:00	Customer 1	Germany	C1_A_1	2	5
2022-01-01 01:00	Customer 1	Germany	C1_A_1	3	6
2022-01-01 02:00	Customer 1	Germany	C1_A_1	4	7
2022-01-01 03:00	Customer 1	Germany	C1_A_1	5	8
2022-01-01 00:00	Customer 1	Italy	C1_A_2	3	4
2022-01-01 01:00	Customer 1	Italy	C1_A_2	4	1
2022-01-01 02:00	Customer 1	Italy	C1_A_2	5	2
2022-01-01 03:00	Customer 1	Italy	C1_A_2	6	8
2022-01-01 00:00	Customer 2	Germany	C2_A_1	3	4
2022-01-01 01:00	Customer 2	Germany	C2_A_1	4	1
2022-01-01 02:00	Customer 2	Germany	C2_A_1	5	2
2022-01-01 03:00	Customer 2	Germany	C2_A_1	6	8
2022-01-01 00:00	Customer 2	Germany	C2_A_2	4	2
2022-01-01 01:00	Customer 2	Germany	C2_A_2	4	9
2022-01-01 02:00	Customer 2	Germany	C2_A_2	5	9
2022-01-01 03:00	Customer 2	Germany	C2_A_2	7	7
2022-01-01 00:00	Customer 2	Italy	C2_A_3	5	9
2022-01-01 01:00	Customer 2	Italy	C2_A_3	7	1
2022-01-01 02:00	Customer 2	Italy	C2_A_3	5	2
2022-01-01 03:00	Customer 2	Italy	C2_A_3	6	8
2022-01-01 00:00	Customer 3	Germany	C3_A_1	5	2
2022-01-01 01:00	Customer 3	Germany	C3_A_1	6	8
2022-01-01 02:00	Customer 3	Germany	C3_A_1	4	9
2022-01-01 03:00	Customer 3	Germany	C3_A_1	5	9
2022-01-01 00:00	Customer 3	Italy	C3_A_2	5	2
2022-01-01 01:00	Customer 3	Italy	C3_A_2	6	8
2022-01-01 02:00	Customer 3	Italy	C3_A_2	9	8
2022-01-01 03:00	Customer 3	Italy	C3_A_2	4	2

From this table I want to create a measure so I can aggregate the absolute difference between the forecast and the value column AND filter based on Area and Customer. This way I can test different combinations of customers and see which combination have the lowest absolute difference.

[ToddChitt]

Can you give us better data sample? I see Forecast, but not Value. And be sure to include a snippet of what your desired output might look like.

If you are looking for an absolute value, then do something like this:

My Measure = SUM ( ABS ( [Forecast] - [Value] ) )

[simber]

I kind of solved it when I'm only calculating at the lowest level (hourly).

I still have problems aggregating it to a higher time resolution so something is still off...

tot_abs = calculate(ABS(SUM(Table[difference])))

I calculate the difference in power query.