topic Calculate dynamic average of subset of years in DAX Commands and Tips

Calculate dynamic average of subset of years

ThomasSan — Wed, 19 Jan 2022 17:19:05 GMT

Hi everyone,

I have data that only begins halfway through my total period as can be seen in this graph:

With "Sales by SFC" only beginning in 2018, the value for "SFC Sales on Total Sales" also only starts in that very same year. Also, there is a year slicer on my page in which I can restrict the current view to only a subset of years such as 2020-2022.

Now, I would like to create an average of "SFC Sales on Total Sales" that only encompasses the years 2018-2022 (creating a general average would skew the result downwards due to the 0s in the previous years). My attempt in doing so looks as follows:

SFC Sales on Total Sales (2018 onwards) = var skewedaverage = DIVIDE( sum(SalesOnSFC[Sales by SFC]), sum(TotalSales[Sales]), blank()) var correctaverage = calculate( skewedaverage, year('Date'[Date]) >= 2018 ) return correctaverage

The problem with my DAX code is that it disregards my year slicer settings in its calculations. In other words, my calculated average is static. This is a problem when the slicer is set to a time frame like 2019-2021. Can therefore someone please help me modifying my code so that it calculates the average dynamically in accordance to the settings of the year slicer?

Thank you in advance!

Re: Calculate dynamic average of subset of years

ValtteriN — Wed, 19 Jan 2022 18:05:01 GMT

Hi,

Here is one way to do this:

After 2019 =

var _year = MAX('Calendar'[Year]) return

CALCULATE(SUM(SlicerYears[Value]),all(SlicerYears),SlicerYears[Year]=_year,SlicerYears[Year]>=2019)

So include var _year and ALL to your CALCULATE.

End result:

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

Re: Calculate dynamic average of subset of years

ThomasSan — Thu, 20 Jan 2022 07:24:28 GMT

Thank you for your reply, ValtteriN !

Do you mean modifying my formula so that it reads as follows

SFC Sales on Total Sales (2018 onwards) = var maxyear = MAX('Date'[Year]) var skewedaverage = DIVIDE( sum(SalesOnSFC[Sales by SFC]), sum(TotalSales[Sales]), blank()) var correctaverage = calculate( skewedaverage, ALL('Date'[Year]), year('Date'[Date])=maxyear, year('Date'[Date]) >= 2018 ) return correctaverage

However, in doing so, it appears that the lower bound is disregarded now (i.e. values from earlier than 2018 are included so the average is skewed down again as the 0s are now also factored in).

Re: Calculate dynamic average of subset of years

ValtteriN — Thu, 20 Jan 2022 07:44:07 GMT

Hi,

The correctaverage should reference facttable except for the maxyear variable. As you can see with my example I am referring to 'SlicerYears' which is my fact table.

Re: Calculate dynamic average of subset of years

ThomasSan — Thu, 20 Jan 2022 08:46:39 GMT

ValtteriN

Oh, I got what you meant. This is my code at the moment:

SFC Sales on Total Sales (2018 onwards) = var maxyear = MAX('Date'[Year]) var sfcsales= calculate( sum(SalesOnSFC[Sales by SFC]), ALL(SalesOnSFC), year(SalesOnSFC[date])=maxyear, year(SalesOnSFC[date]) >= 2018 ) var allsales= calculate( sum(TotalSales[Sales]), ALL(TotalSales), year(TotalSales[date])=maxyear, year(TotalSales[date]) >= 2018 ) var ratio = DIVIDE( sfcsales, allsales, blank() ) return ratio

Here is also a screenshot of my data modell regarding all three tables involved

However, it seems that now I only get the latest value returned

Re: Calculate dynamic average of subset of years

ValtteriN — Thu, 20 Jan 2022 08:52:32 GMT

Hi ThomasSan ,

Let's step back a bit, now that I see your visual I have another idea. You could just include a visual level filter where year>2018 and then calculate the ratio normally.

Re: Calculate dynamic average of subset of years

ThomasSan — Thu, 20 Jan 2022 11:51:17 GMT

ValtteriN

That was the final key. So the following code is the one that works:

SFC Sales on Total Sales (2018 onwards) = var sfcsales= calculate( sum(SalesOnSFC[Sales by SFC]), year(SalesOnSFC[date]) >= 2018 ) var allsales= calculate( sum(TotalSales[Sales]), year(TotalSales[date]) >= 2018 ) var ratio = DIVIDE( sfcsales, allsales, blank() ) return ratio