Forecast Accuracy: Calculate the Category average based on percentages subcategories

[lorenzo_cassina]

Hello friends,

I'm struggling with this issue for like a month, anybody could help me please?

I have two tables:
Forecast Table:
Account
Category 
Subcategory
SubSubcategory
Forecasted Value

Actual Table:
Account
Category 
Subcategory
SubSubcategory
Actual Value

I would like to calculate my forecast accuracy in two methods (like in the simplified table below) :
- Weighted Average Accuracy (easy, I got this)
- Non Weighted Average of the Subcategory Accuracies (in yellow)

After this, I need to be able to filter the results by "Account" with a slicer on my dashboard.
This will allow me to understand how well the Account has forcasted for each Category, Subcategory and the third is a KPI that kind of average the two.
I think this could be done with some DAX VAR or GROUPBY but didn't manage to do make it right now.. Any ideas folks?
 

[v-jayw-msft · Accepted solution]

Hi @lorenzo_cassina ,

Is [Weighted Average Accuracy] a measure?

Try this formula:

measure = averagex(filter(table,table[category]=selectedvalue(table[category])),[Weighted Average Accuracy])

Best Regards,

Jay