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.
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
Solved! Go to Solution.
Hi,
Problem solved. You may download my PBI file from here.
Hope this helps.
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.
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.
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
Hi,
Problem solved. You may download my PBI file from here.
Hope this helps.
Hi,
If i drag the month from your non calendar table, the formula will still work but i always choose to create a Calendar Table so that i can relate other tables to this one as well.
Hi @Ashish_Mathur,
I get a different result if I used the Table1[Month] as appose to the 'Month Lookup'[Month]. Only thing changed is which Month is dropped into values
If you drop the months from th Table1 table, then in all your formulas also, you will have to use Table1[Month]
Yes but if I do that the result is still wrong.
Average Accuracy = AVERAGEX( VALUES(Table1[Month]), [Variance % - Forecast to Actual] )
Measure = CALCULATE([Average Accuracy],ALL(Table1[Month]))
Still gives the result pictured above. However if I remove the relationship between the tables it seems to work?
I may have made some other change there as well. Please compare both models yourself. I am working on another question so cannot devote time to this now. Sorry.
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?
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 |
---|---|
107 | |
98 | |
78 | |
65 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |