Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
ThomasSan
Helper IV
Helper IV

Calculate dynamic average of subset of years

Hi everyone,

 

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

ThomasSan_0-1642611693979.png

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!

1 ACCEPTED SOLUTION

@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  

View solution in original post

6 REPLIES 6
ValtteriN
Super User
Super User

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:

ValtteriN_0-1642615476709.pngValtteriN_1-1642615491616.png

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!






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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).

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@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 

ThomasSan_1-1642668236049.png

 

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

ThomasSan_2-1642668381693.png

 

 

 

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. 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@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  

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors