Forum Discussion

hyby_tt's avatar
hyby_tt
Frequent Visitor
4 years ago
Solved

Cumulative sum of absolute differences

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 :

MonthBudgetActualRelative differenceAbsolute differenceWhat I try to calculate
January1000900100100100
February8001000-200200300
March12001300-100100400


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

MonthTeamtypeBudgetActualtot budgettot Actual
JanuaryA12503001000900
JanuaryA2250200
JanuaryB1250150
JanuaryB2250300
FebruaryA12001008001000
FebruaryA2200300
FebruaryB1200200
FebruaryB2200200
MarchA130040012001300
MarchA2300200
MarchB1300300
MarchB2300400


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 ?
Absolute Value
Cumulative Calculation
SUMMARIZE
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    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.

4 Replies

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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's avatar
    vapid128
    Icon for Solution Specialist rankSolution Specialist
    4 years ago

    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's avatar
      hyby_tt
      Frequent Visitor
      4 years ago

      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

  • vapid128's avatar
    vapid128
    Icon for Solution Specialist rankSolution Specialist
    4 years ago

    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