Cumulative sum of absolute differences

[hyby_tt]

Hello,

The issue :

We try to measure the precision of our teams between the budget they target and what they actually spend.
So it's important that we use the cumulative of absolute differences (however the delta is negative or positive)
I have many line in my dataset because users can filter on many things

To simplify what I try to achieve I made this table :

Month	Budget	Actual	Relative difference	Absolute difference	What I try to calculate
January	1000	900	100	100	100
February	800	1000	-200	200	300
March	1200	1300	-100	100	400

The table looks like something like (2 last columns make the link with the previous table) :

Month	Team	type	Budget	Actual	tot budget	tot Actual
January	A	1	250	300	1000	900
January	A	2	250	200
January	B	1	250	150
January	B	2	250	300
February	A	1	200	100	800	1000
February	A	2	200	300
February	B	1	200	200
February	B	2	200	200
March	A	1	300	400	1200	1300
March	A	2	300	200
March	B	1	300	300
March	B	2	300	400

What I tried :

var f = CALCULATE(SUM(Actuals_Profiling[Cost]),Actuals_Profiling[cost_type]="Actual")

var g = CALCULATE(SUM(Actuals_Profiling[Cost]),Actuals_Profiling[cost_type]="Profiling")

var h = ABS(g-f)

var i =

sumx(SUMMARIZE('Working days',

'Working days'[fmonth],

"absolute",

h),[absolute])

return

i

It doesnt work
any idea ?

[v-yanjiang-msft · Accepted solution]

Hi @hyby_tt ,

According to your description, Actual and Budget are two columns in the table, but in your formula, it seems not the same structure. Based on your formula, I create a sample.

You have two tables: Actuals_Profiling and Working days. I'm not clear about the relationship, so there's only one table in my sample. Here's my solution.

Create two measures.

Absolute difference = 
VAR f =
    CALCULATE (
        SUM ( Actuals_Profiling[Cost] ),
        FILTER(ALL('Actuals_Profiling'),Actuals_Profiling[cost_type] = "Actual"&&'Actuals_Profiling'[Month]=MAX('Actuals_Profiling'[Month])
    ))
VAR g =
    CALCULATE (
        SUM ( Actuals_Profiling[Cost] ),
        FILTER(ALL('Actuals_Profiling'),Actuals_Profiling[cost_type] = "Profiling"&&'Actuals_Profiling'[Month]=MAX('Actuals_Profiling'[Month])
    ))
VAR h =
    ABS (g-f)

RETURN
    h

Accumulate difference =
SUMX (
    FILTER (
        ALL ( 'Actuals_Profiling' ),
        'Actuals_Profiling'[Month] <= MAX ( 'Actuals_Profiling'[Month] )
    ),
    'Actuals_Profiling'[Absolute difference]
)
    / COUNTROWS (
        FILTER (
            ALL ( 'Actuals_Profiling' ),
            'Actuals_Profiling'[Month] = MAX ( 'Actuals_Profiling'[Month] )
        )
    )

Get the result.

I attach my sample for your reference.

Best Regards,
Community Support Team _ kalyj

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

[vapid128]

That is my table, just copy the table and copy the Measure and Measure 2

Month Team type Budget Actual Month_Number
January A 1 250 300 1
January A 2 250 200 1
January B 1 250 150 1
January B 2 250 300 1
February A 1 200 100 2
February A 2 200 300 2
February B 1 200 200 2
February B 2 200 200 2
March A 1 300 400 3
March A 2 300 200 3
March B 1 300 300 3
March B 2 300 400 3

[vapid128]

I am confused where are the total Actual come from.

Jan 300+200+150+300 = 950

Feb 100+300+200+200 =800

If it is only calculation mistake:

Measure = SUMX(VALUES('Table'[Month]),CALCULATE(ABS(SUM('Table'[Actual])-SUM('Table'[Budget]))))

Measure 2 = 
var monthindex = MAX('Table'[Month_Number])
return
CALCULATE([Measure],FILTER(ALLSELECTED('Table'),[Month_Number]<=monthindex))

[hyby_tt]

Hello,

Thank you for your help,

You assumed correctly, I made a mistake and the the total by month should match between first and seconde table.
However I tried your solution and it doesn't work for me