 # Calculate total of averages

Hi there,

I have a dataset starting from 2012 until now, following this pattern :

|Date|Month|Year|Production|
|01.01.2019|January|2019|18125.611|
|01.02.2019|Febuary|2019|14724.025|
|01.03.2019|March|2019|14273.148|
|01.04.2019|April|2019|10659.834|
|01.05.2019|May|2019|7536.206|
|01.06.2019|June|2019|5213.396|
|01.07.2019|July|2019|5710.79|
|01.08.2019|August|2019|5757.512|
|01.09.2019|September|2019|5540.249|
|01.10.2019|October|2019|6779.019|
|01.11.2019|November|2019|11208.418|
|01.12.2019|December|2019|4119.429|

For each month of each year I do the following :
Budget January 2013 = Production January 2012
Budget January 2014 = (Production January 2012 + Production January 2013) / 2
Budget January 2015 = (Production January 2012 + Production January 2013 + Production January 2014) / 3

and I need the total to be the sum of the monthly averages i.e. :
average of January + average of Febuary + …

Production Budget =
VAR _min = 2012
VAR monthlyBudget= CALCULATE(AVERAGEX(‘Table’,[Production]]),FILTER(ALL(Dates),(Dates[Year]>=_min && Dates[Year]<MAX(Dates[Year])) && Dates[MonthName]=MAX(Dates[MonthName])))
RETURN
IF(HASONEVALUE(Dates[MonthName]),
monthlyBudget,
SUMX(VALUES(Dates[MonthName]),monthlyBudget)
)

The average for each month works fine, however the total I get is wrong and I don’t know what to change in the formula to fix it.
Can somebody help me ?

Thanks !

Hello @DawnLs,

Thank you for posting your qury onto the Forum.

Well it’s always a tough task to carry out a formula in the absence of a PBIX file.

But you can try out the formula given below -

``````Production Budget =
VAR _min = 2012

VAR monthlyBudget = CALCULATE(
AVERAGEX( ‘Table’,[Production] ),
FILTER( ALL( Dates ) ,
( Dates[Year] >= _min && Dates[Year] < MAX( Dates[Year] ) ) &&
Dates[MonthName] = MAX( Dates[MonthName] ) ) )

RETURN
IF( HASONEVALUE( Dates[MonthName] ) ,
monthlyBudget,
SUMX(
SUMMARIZE( Dates[MonthName] , "Averages" , monthlyBudget ) , [Averages] ) )
``````

Hoping this formula works out for you. Thanks & Warm Regards,
Harsh

Hi @Harsh,

I tried the formula you suggested but didn’t get the result I expected for the total.

Here is the pbix file ( I just kept 2 years of data to simply). Hope it will help.
Thanks !

avg-problem.pbix (139.4 KB)

Hello @DawnLs,

This happened due to the variables which were set inside the Budget Production DAX formula.

So this will be calculated in two different parts -

Part 1: Where you’ll just calculate your Averages and the formula will be as follows -

``````Averages =
VAR _min = 2018

VAR monthlyBudget = CALCULATE(
AVERAGEX('Production' , Production[Production] ) ,
FILTER( ALL( Dates ) ,  Dates[Year] >= _min && Dates[Year] <= MAX(Dates[Year] )
&& Dates[MonthName] = MAX( Dates[MonthName] ) ) )

RETURN
IF( HASONEVALUE( Dates[MonthName] ),
monthlyBudget,
BLANK() )
``````

Part 2: Budget Production (Average Total)

`````` Budget Production (Average Total) =
SUMX(
SUMMARIZE( Dates , Dates[MonthName] , "Averages" , [Averages] ) , [Averages] )
``````

By this way you’ll achieve the desired result that you’re looking for.

Sam has explained many a times where to use our variables wisely or else our Totals can differ widely.

I’m attaching the screenshot as well as the PBIX file of my working for the reference.

Hoping you find this useful and helps you in your analysis.

Thanks & Warm Regards,
Harsh

avg-problem.pbix (136.8 KB)

1 Like

Fixed it.
avg-problem.pbix (130.9 KB)

``````Production Budget =
SUMX (
ALLSELECTED ( Dates[MonthName] ),
CALCULATE (
AVERAGEX ( 'Production', [Production] ),
SAMEPERIODLASTYEAR ( Dates[Date] )
)
)``````

Thanks a lot.

Thanks for your answer but this solution only works when there is only one year of historical, and in this case I have to take into account several years…

Hello @DawnLs,

You’re Welcome. Hoping you will find this useful and helps you in your future analysis. Best of luck. 