Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello to the forum,
I have the following table Sales:
Date | Amount |
2022-01-01 | $220,00 |
2022-02-01 | $632,00 |
2022-03-01 | $647,00 |
2022-04-01 | $229,00 |
2022-05-01 | $231,00 |
2022-06-01 | $880,00 |
2022-07-01 | $306,00 |
2022-08-01 | $777,00 |
2022-09-01 | $788,00 |
2022-10-01 | $631,00 |
2022-11-01 | $737,00 |
2022-12-01 | $702,00 |
2023-01-01 | $209,00 |
2023-02-01 | $177,00 |
2023-03-01 | $800,00 |
2023-04-01 | $310,00 |
2023-05-01 | $360,00 |
2023-06-01 | $121,00 |
2023-07-01 | $216,00 |
2023-08-01 | $320,00 |
2023-09-01 | $435,00 |
2023-10-01 | $377,00 |
2023-11-01 | $300,00 |
2023-12-01 | $785,00 |
I connected the Sales table to the Dates table.
Now I want to calculate the average sales. My goal is to always calculate the average over exactly one year. This result should come out:
To do this, I create the following measure:
Average Sales =
CALCULATE(
AVERAGE('Sales'[Amount]),
ALL(Dates[Month])
)
I think the expression ALL(Dates[Month]) should now remove the filter context for the month, leaving the filter context for the year. So the result from the 2nd table should appear.
In fact, I get this result:
Where is my misconception?
Thanks!
Solved! Go to Solution.
Hi,
you could use the same measure you have but instead of ALL use ALLEXCEPT and join only on the year column. e.g.
Average Sales =
CALCULATE(
AVERAGE('Sales'[Amount]),
ALLEXCEPT(Sales[Year])
)
if you don't have a year column:
If I answered your question, please mark my post as solution, Appreciate your Kudos 👍
It might still be inferring context from Dates[Date].
Try ALLEXCEPT(Dates[Year]) in place of ALL(Dates[Month]).
Or try using:
AVERAGEX(VALUES(Dates[Year]),Sales[Amount])
Hi,
you could use the same measure you have but instead of ALL use ALLEXCEPT and join only on the year column. e.g.
Average Sales =
CALCULATE(
AVERAGE('Sales'[Amount]),
ALLEXCEPT(Sales[Year])
)
if you don't have a year column:
If I answered your question, please mark my post as solution, Appreciate your Kudos 👍
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |