Calculate average of a column then subtract that value from each row in the column

DAX DAX Calculations
1

Hi there,

I am trying to calculate the Mean Absolute Percent Variation (MAPV) of my sales demand, which is defined as equal to the average absolute deviation from the historical mean demand divided by the historical mean demand and expressed as a percent.

To do so, I need to:

  1. Calculate the average demand.
  2. Calculate the absolute deviation as the difference between the sales demand minus the average demand by row.
  3. Calculate the average absolute deviation.
  4. Divide the average abs deviation by the average demand to get my metric.

I am getting stuck on point 2. I am trying to run this calculation so I can see the MAPV by month, here is my formula:

Abs Deviation =
VAR avg_demand =
CALCULATE (
AVERAGE ( Demand_Forecast_Fact[Demand] ),
DATESBETWEEN (
Date_Lookup[Date],
STARTOFMONTH ( Date_Lookup[Date] ),
ENDOFMONTH ( Date_Lookup[Date] )
)
)
RETURN
ABS ( SUM ( Demand_Forecast_Fact[Demand] ) - avg_demand )

In theory, this formula should remove the date filter from the table and calculate the monthly average for the month selected. It looks like it’s working at a row level.

image
image614×689 25.1 KB

However, the total value is incorrect.

As you can see the Abs Deviation at the bottom of the table should be (|15-3| + |30-12| + |3-12| + |0-12|) = 42. Instead it is showing 36 which I assume is Total Demand - Average(|48-12|).

Any idea on how to solve this?

Thanks,
Alberto.

2

If in addition to the addition you want to remove the blank demands from the visual, see if it helps

Abs Desviation =
SUMX (
    SUMMARIZECOLUMNS ( dCalendar[Date], dCalendar[Calendar Month Year] ),
    CALCULATE (
        ABS (
            [Demand]
                - IF (
                    NOT ISBLANK ( [Demand] ),
                    CALCULATE ( [Avg Demand], REMOVEFILTERS ( dCalendar[Date] ) )
                )
        )
    )
)
3

Hi Vilmar,

Thanks very much for providing this solution, it worked!

Regards,
Alberto.