Calculate total of averages

DAX DAX Calculations
1

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 !

2

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. :slightly_smiling_face:

Thanks & Warm Regards,
Harsh

3

Hi @Harsh,

Thanks a lot for your answer.
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)

4

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.

Average Total
Average Total1272×715 83.9 KB

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

Thanks & Warm Regards,
Harsh

avg-problem.pbix (136.8 KB)

1 Like
5

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

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

Thanks a lot.
Indeed it was helpful !

7

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…

8

Hello @DawnLs,

You’re Welcome. :slightly_smiling_face:

I’m glad I was able to help you.

If it’s OK to you I would really like to recommend you one tip which may prove helpful to you that is always one should always go for the “Measure Branching” technique first and once that technique provides you the desired results then you can combine those formulas in the form of “Variables” and put it in one formula.

Because by following the “Measure Branching” one can analyze the results and the steps about where they went wrong or have faltered and if we directly go for “Variables” we may get lost and may also find it difficult to analyze where we went wrong.

I’ve learned this technique from Sam where he has always recommended to built - up or start with small measures so that we know what process we are following and whether that process will provide the fruitful results or not.

Hoping you will find this useful and helps you in your future analysis. Best of luck. :+1:

Thanks & Warm Regards,
Harsh