Average Accuracy by Month

[Anonymous]

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

[Ashish_Mathur · Accepted solution]

Hi,

Problem solved.  You may download my PBI file from here.

Hope this helps.

[Anonymous · Accepted solution]

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.

[Ashish_Mathur]

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.

[Anonymous]

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

[Ashish_Mathur · Accepted solution]

Hi,

Problem solved.  You may download my PBI file from here.

Hope this helps.

[Anonymous]

Thanks @Ashish_Mathur.

Why does referencing the month in the other table make a difference?

Thanks

[Ashish_Mathur]

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.

[Anonymous]

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

[Ashish_Mathur]

If you drop the months from th Table1 table, then in all your formulas also, you will have to use Table1[Month]

[Anonymous]

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?

[Ashish_Mathur]

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.

[Phil_Seamark]

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?

[Anonymous]

Hi @Phil_Seamark. Yes it should be the average.