Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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!
Solved! Go to Solution.
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
Hi,
Here is one way to do this:
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!
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.
Proud to be a Super User!
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
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.
Proud to be a Super User!
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
24 | |
21 | |
20 | |
14 | |
11 |
User | Count |
---|---|
43 | |
35 | |
25 | |
24 | |
23 |