Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi Guy,
I'm trying to calculate current Year Avg sales. when i filter to current Year i get this value with sales for this year instead Avg. see DAX below
Solved! Go to Solution.
Hi @Anonymous ,
I agree with vapid128 's reply. I think your average is calcualted based on years and sales measure. I think sales measure should be created by SUM() function.
My Sample:
Measure:
Sales_ = SUM('Table'[Sales])
Avg Sales =
CALCULATE(AVERAGEX(VALUES('Calendar'[Fiscal Year]),[Sales_]))
Result:
You see when I select 2022, I could only get average the same as the total in 2022. If you sum to calculate the sales, Power BI will take the same year as a whole.
What kind of result do you want? If you want to get 100 as average in 2022, please do not use sales measure.
Try this code.
Avg Sales =
AVERAGEX('Table','Table'[Sales])
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I am a little bit missunderstand,
your [sales] should be a sum of somthing
Let's say 2021 sales = 200, 2020 sales =100
If you select 2021 2020, [sales] = 300, [avg sales] = 150
if you select 2021, [sales] = 200, [avg sales] =200
everytime when you select one year, it is the sales value.
@vapid128 Everything i select 2022, it give me the same as Total year instead of avg
Hi @Anonymous ,
I agree with vapid128 's reply. I think your average is calcualted based on years and sales measure. I think sales measure should be created by SUM() function.
My Sample:
Measure:
Sales_ = SUM('Table'[Sales])
Avg Sales =
CALCULATE(AVERAGEX(VALUES('Calendar'[Fiscal Year]),[Sales_]))
Result:
You see when I select 2022, I could only get average the same as the total in 2022. If you sum to calculate the sales, Power BI will take the same year as a whole.
What kind of result do you want? If you want to get 100 as average in 2022, please do not use sales measure.
Try this code.
Avg Sales =
AVERAGEX('Table','Table'[Sales])
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
averagex(values([year]),calculate([sales]))
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
25 | |
17 | |
12 | |
12 | |
10 |
User | Count |
---|---|
33 | |
25 | |
16 | |
14 | |
13 |