cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Create an accurate forecasted at completion measure

Hi,

Can anyone help or come up with a good way to work out the most accurate forecast at completion measure. The forecasted at completion currently within the dashboard is just working on IF logic where, if the current actual is greater than the budget then it just uses that as the FAC, if the current is less then it uses the Budget as the FAC.

I have a field 'elementcompletedstatus' that shows Yes when the level/element is completed. Could i use the the average variance from the budget for completed elements & levels then for the same elements on uncompleted levels that average gets added to the budget and that becomes the new FAC?

Would there be a way to ignore areas where there is no budget or if there is a ridiculous % blow out becasue a small area goes over by a lot?

Dashboard is saved in the onedrive link below. thanks so much
Power BI

1 ACCEPTED SOLUTION
Memorable Member

@jpdamd90 if the goal is to ensure that the Forecast At Completion (FAC) for uncompleted elements is calculated based on the average variance of completed elements of the same type (Element) and Level, you can do this steps:

1. Create a measure for the variance calculation for completed levels:

Variance =
IF(
'Table'[COMPLETE] = "YES",
DIVIDE('Table'[ACTUAL SUPPLY (\$)] - 'Table'[BUDGET SUPPLY (\$)], 'Table'[BUDGET SUPPLY (\$)], 0),
BLANK()
)

2. Create a measure to calculate the average variance for each element:

Average Variance =
CALCULATE(
AVERAGE('Table'[Variance]),
ALLEXCEPT('Table', 'Table'[Element])
)

3. Create a calculated column for the FAC:

FAC =
IF(
ISBLANK('Table'[Variance]),
IF(
('Table'[BUDGET SUPPLY (\$)] * (1 + 'Table'[Average Variance])) > 'Table'[ACTUAL SUPPLY (\$)],
'Table'[BUDGET SUPPLY (\$)] * (1 + 'Table'[Average Variance]),
'Table'[ACTUAL SUPPLY (\$)]
),
'Table'[ACTUAL SUPPLY (\$)]
)

In this way, the FAC for uncompleted elements will reflect the budget plus the average variance of the same element type, ensuring accurate forecasting.

BBF

3 REPLIES 3
Memorable Member

@jpdamd90 By following these steps, you can implement a more accurate and robust FAC calculation in Power BI that leverages historical performance data to forecast future costs.

1. Create Calculated Columns for Variance:

Supply_Variance = IF([COMPLETE] = "YES", [ACTUAL SUPPLY (\$)] - [BUDGET SUPPLY (\$)], BLANK())

Install_Variance = IF([COMPLETE] = "YES", [ACTUAL INSTALL (\$)] - [BUDGET INSTALL (\$)], BLANK())

Supply_Percent_Variance = IF([COMPLETE] = "YES", ([ACTUAL SUPPLY (\$)] - [BUDGET SUPPLY (\$)]) / [BUDGET SUPPLY (\$)] * 100, BLANK())

Install_Percent_Variance = IF([COMPLETE] = "YES", ([ACTUAL INSTALL (\$)] - [BUDGET INSTALL (\$)]) / [BUDGET INSTALL (\$)] * 100, BLANK())

2. Calculate Average Variance:

Average_Supply_Variance = AVERAGEX(FILTER('Table', 'Table'[COMPLETE] = "YES" && [BUDGET SUPPLY (\$)] > 0), [Supply_Percent_Variance])

Average_Install_Variance = AVERAGEX(FILTER('Table', 'Table'[COMPLETE] = "YES" && [BUDGET INSTALL (\$)] > 0), [Install_Percent_Variance])

3. Apply Average Variance to Incomplete Elements:

Forecast_Supply = IF([COMPLETE] = "NO", [BUDGET SUPPLY (\$)] * (1 + [Average_Supply_Variance] / 100), [ACTUAL SUPPLY (\$)])

Forecast_Install = IF([COMPLETE] = "NO", [BUDGET INSTALL (\$)] * (1 + [Average_Install_Variance] / 100), [ACTUAL INSTALL (\$)])

4. Calculate FAC:

FAC = IF([COMPLETE] = "NO", [Forecast_Supply] + [Forecast_Install], [ACTUAL SUPPLY (\$)] + [ACTUAL INSTALL (\$)])

5. Filter Outliers and Zero Budgets:

Valid_Forecast_Supply = IF([BUDGET SUPPLY (\$)] > 0 && ABS([Supply_Percent_Variance]) <= 200, [Forecast_Supply], BLANK())

Valid_Forecast_Install = IF([BUDGET INSTALL (\$)] > 0 && ABS([Install_Percent_Variance]) <= 200, [Forecast_Install], BLANK())

Valid_FAC = IF(NOT(ISBLANK([Valid_Forecast_Supply])) && NOT(ISBLANK([Valid_Forecast_Install])), [Valid_Forecast_Supply] + [Valid_Forecast_Install], BLANK())

BBF

Helper I

Thanks BBF,

I've created all those columns and the output of the FAC shows as below. How can i change it so that if the Budget + the variance is higher than the current actual that it takes the Budget + variance as the FAC?

Also will the formulas take in to account the element that the variance is associated with? Eg, if Jumpform has 3 levels completed and the average variance of the 3 levels is 6%, and SLAB (NON PT) has 3 levels completed with an average variance of 8% then any uncompleted jumpform elements would show the FAC as budget + 6% where as any SLAB (NON PT) would show as budget + 8%?

Hope that makes sense. Thanks

Memorable Member

@jpdamd90 if the goal is to ensure that the Forecast At Completion (FAC) for uncompleted elements is calculated based on the average variance of completed elements of the same type (Element) and Level, you can do this steps:

1. Create a measure for the variance calculation for completed levels:

Variance =
IF(
'Table'[COMPLETE] = "YES",
DIVIDE('Table'[ACTUAL SUPPLY (\$)] - 'Table'[BUDGET SUPPLY (\$)], 'Table'[BUDGET SUPPLY (\$)], 0),
BLANK()
)

2. Create a measure to calculate the average variance for each element:

Average Variance =
CALCULATE(
AVERAGE('Table'[Variance]),
ALLEXCEPT('Table', 'Table'[Element])
)

3. Create a calculated column for the FAC:

FAC =
IF(
ISBLANK('Table'[Variance]),
IF(
('Table'[BUDGET SUPPLY (\$)] * (1 + 'Table'[Average Variance])) > 'Table'[ACTUAL SUPPLY (\$)],
'Table'[BUDGET SUPPLY (\$)] * (1 + 'Table'[Average Variance]),
'Table'[ACTUAL SUPPLY (\$)]
),
'Table'[ACTUAL SUPPLY (\$)]
)

In this way, the FAC for uncompleted elements will reflect the budget plus the average variance of the same element type, ensuring accurate forecasting.

BBF

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors