Forum Discussion

kmes912's avatar
kmes912
Icon for Helper I rankHelper I
5 years ago
Solved

Aggregate Absolute Value At a More General Level

Hi,

I have data that is at the unit > multiple drivers > label > date. 

What I want to be able to do is get the absolute difference by day by filtering at the unit level. So below is a mock up of the data set current (forecast vs actual column does not exist in the data set). What I want to do is get a result of 59 by combining each day, then take the absolute difference for each day, then sum by unit when I filter a unit. I used this formula, which works when I display the date on the matrix of 0+2+11+3+33+10+0 but on any visual without the day detail, it shows 39 which isnt right. 

 

Forecast vs Actual NEW = abs(sumx('02 Volume Plan Edits','02 Volume Plan Edits'[Actual])-sumx('02 Volume Plan Edits','02 Volume Plan Edits'[Plan Adjusted]))

 

Actual Data Set

UnitDateForecast vs ActualActualPlan AdjustedPOS Label
1234569/26/2020419150010Group
1234569/26/202043343380010Trans
1234569/27/20201210010Group
1234569/27/202013443430010Trans
1234569/28/2020113082970010Trans
1234569/29/202032952920010Trans
1234569/30/2020333062730010Trans
12345610/1/20201210010Group
12345610/1/2020113423530010Trans
12345610/2/20202310010Group
12345610/2/202023523540010Trans

 

Desired Result, which i can get on a matrix if i show date. 

unitDateForecast vs ActualActualPlan AdjustedPOS Label
1234569/26/20200353353ignore
1234569/27/20202346344ignore
1234569/28/202011308297ignore
1234569/29/20203295292ignore
1234569/30/202033306273ignore
12345610/1/202010344354ignore
12345610/2/20200355355ignore

 

But once I only do unit this happens:

unitForecast vs Actual
12345639

 

Had this also, but this ends up with 73 which is the detailed by day abs value.

Forecast vs Actual = (CALCULATE(sumx('02 Volume Plan Edits',abs('02 Volume Plan Edits'[Actual]-'02 Volume Plan Edits'[Plan Adjusted]))))
  • wdx223_Daniel's avatar
    wdx223_Daniel
    5 years ago

    think you need a sumx to bracket the original measure.

    AbsDiff:=SUMX(VALUES(Table2[Date]),CALCULATE(ABS(SUM(Table2[Actual])-SUM(Table2[Plan Adjusted]))))

1 Reply

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Icon for Community Champion rankCommunity Champion
    5 years ago

    think you need a sumx to bracket the original measure.

    AbsDiff:=SUMX(VALUES(Table2[Date]),CALCULATE(ABS(SUM(Table2[Actual])-SUM(Table2[Plan Adjusted]))))