Forum Discussion
Average Accuracy by Month
Hi All,
I am looking to calculate an average forecasting accuracy by month. I have created the following measure which works as expected (see results in pic below)
Forecasting Accuracy =
AVERAGEX(
VALUES(Dim_Date[Month]),
[Variance % - Forecast to Actuals]
)However I am looking to create an average of this accuracy across the 12 months. I have placed a CALCULATE around the measure however this doesn't appear to work. I am trying to get the 69.49% in the total to appear in each months column.
Average Forecasting Accuracy =
CALCULATE(
AVERAGEX(
VALUES(Dim_Date[Month]),
[Variance % - Forecast to Actuals]
),
ALL(Dim_Date[Month])
)Many thanks
For others who potentially have this issue in the future if the column you are calculating ALL over (in my case Dim_Date[Month]) has a Sort by Column set to something else then you will need to include it in the ALL function.
In my case Month is sorted by Period and therefore I need to change my ALL clause from ALL(Dim_Date[Month]) to ALL(Dim_Date[Month], Dim_Date[Period]).
This seems quite odd and I am unsure if this is intentional and I have set something up wrong or if this is a bug.
12 Replies
Hi Anonymous
What do you want the total for the [Average Forecasting Accuracy] measure to be? Should it be the Average of the 12 numbers above it?
Hi Phil_Seamark. Yes it should be the average.
Hi,
Does this work?
=CALCULATE([Forecasting Accuracy],ALL(Dim-Date[Month]))
If it does not, then share the link from where i can download your PBI file.
Hi Ashish_Mathur.
I have created a sample dataset in a pbi file here https://www.dropbox.com/s/wg6qcy25zxx9m9j/Powe%20BI%20Forum%20Question.pbix?dl=0
Many thanks for your help.
Thanks
For others who potentially have this issue in the future if the column you are calculating ALL over (in my case Dim_Date[Month]) has a Sort by Column set to something else then you will need to include it in the ALL function.
In my case Month is sorted by Period and therefore I need to change my ALL clause from ALL(Dim_Date[Month]) to ALL(Dim_Date[Month], Dim_Date[Period]).
This seems quite odd and I am unsure if this is intentional and I have set something up wrong or if this is a bug.
