cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rachel_g
Helper I
Helper I

Calculate the average of a measure that is returning a percentage variance

Hi all

 

Can someone please assist. I have some data over the course of a financial year and I am building a forecasting model. I have created two measures, one tracking variance between actual and budget as $, and one as a %. My two variance measures are: 

 

Var Budget vs Actual = IF([Total Units]<>0, [Total Units]-[Budget Units], "")
Var % Budget vs Actual = IF([Total Units]<>0,(([Total Units]-[Budget Units])/[Budget Units]), "")

 

I want to create another separate measure that gives me the actual average % for the entire FYTD (excluding current month and the months up to end of the financial year), so I can use it outside of the table in other calculations (i.e. as the factor for my forecasting model, rather than using flat, hard coded factors of 5% or 20% growth).

 

Can anyone assist me with the DAX for this measure, please? Can't seem to get it to work. Below is my table of data, for context. 

 

 

 

PBI snip.PNG

3 REPLIES 3
yingyinr
Community Support
Community Support

Hi @rachel_g ,

Do you want to forecast the actual value from May 2021 to September 2021? If so, maybe you can refer to the content in the links below to implement it.

Creating Forward Forecasts in Power BI Using DAX

Showing actuals and forecasts in the same chart with Power BI

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I have already built the forecast formula and it works perfectly, but I am using hard coded values in it (i.e. 1.05 for 5%) and I want to use the actual average % variance between actual and budget so that it is a dynamic % that adjusts the forecast. Here is my forecasting formula:

 

Forecast Logs (+5%) =
VAR UnitsLY = CALCULATE([Total Units], DATEADD(DateTable[Date],-1,YEAR))
VAR Units2yrAgo = CALCULATE([Total Units], DATEADD(DateTable[Date], -2, YEAR))
VAR Factor = 1.05
RETURN
DIVIDE([UnitsLY]+[Units2yrsAgo],2,0)*Factor

 

So I just need assistance with the measure itself, to give me the actual average % for the entire FYTD (excluding current month and the months up to end of the financial year)

 

If you see the image above, the column Var % Budget vs Actual gives a % variance between the Actual and Budget columns, and if you average it (excluding the variance of the current and future months in the financial year), you get a % value. That is the value I want to be able to grab and use in my forecasting formula as a measure, instead of a hard coded factor of 1.05 (5%) as it shows more accurately what we are likely to finish the year on, above budget. I'm having trouble getting the syntax of this to work in a measure.

yingyinr
Community Support
Community Support

Hi @rachel_g ,

What's the calculation logic of var Factor? The below formula is correct or not?

Factor= average((Budget/Actual)/(Actual-Budget))

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors