Forum Discussion
Calculating value from table values
I have created a table and need a formula to calculate the values in a vacant column. The relevant columns in the table are shown below. I want to calculate the Variance, which is the the difference between "Budget" and "Actuals to Date" plus "Forecast to Complete", as a percentage.
The Budget, Actuals to Date and Forecast to Complete all come from different data tables. The Budget and Actuals are sums and the Forecast is filtered to only show the latest amount.
Any help appreciated.
Steve
Thanks Thejeswar
I am happy with the result. We round the variance value to a whole number, so it will be 0% in this case, which is what would be expected.
Cheers
10 Replies
I appreciate your help on this matter but I am not in a position to share the PBIX file.
I take that the solution is not a simple matter of a formula that uses the values in the table?
Thanks
Steve
Hi kennoe,
Are Budget, Actuals to Date and Forecast calculated columns/measures or original fields in source data tables? You could provide some dummy data to make the source table structure and relationships more clear.
Regards,
Yuliana Gu
I have extracted data from the tables to try to explain what is required. These three tables are where the base data is drawn.
Table 1 - Budget
IdentifierId Funding Status Funding Type Budget 5 Planned OPEX 136244 5 Allocated OPEX 136244 5 Released OPEX 136244 5 Released OPEX 15400 9 Planned CAPEX 358125 9 Allocated CAPEX 358125 9 Released CAPEX 358125 9 Planned OPEX 77948 9 Allocated OPEX 77948 9 Released OPEX 77948 9 Planned OPEX 181880 9 Allocated OPEX 181880 9 Released OPEX 181880 9 Planned OPEX 103567 9 Planned OPEX 517835 Table 2 Actuals
IdentifierId Actuals to Date Expense Type 5 72680 OPEX 5 11334 OPEX 5 43555 OPEX 9 966 OPEX 9 8574 OPEX 9 298425 CAPEX 9 19614 OPEX
Table 3 - Forecast
Title IdentifierId Forecast to Complete Forecast for period 1Aug2018 to 31Aug2018 5 14112 Forecast for period 1Aug2018 to 31Aug2018 9 289893 The report shows the Budget as sum of CAPEX and OPEX amounts, with a Status of "Released" for each identifier, Actuals as the sum of all amounts for each of the identifier, and for Forecast, the table is filtered so only the most recent amount for each identifier is shown. For project 5 it would look like this:
Budget Actuals Forecast 5 151644 127569 14112 I want to calculate the variance between actual costs plus what is forecast and the budget, which is calculated as "(Budget/(Actual + Forecast"))/Budget". For project 5 this calculates as 0%.
If this is too hard to do, I will fall back to Plan B and export the report to excel and add the variance to the report there.
Hope this helps
Hi kennoe,
Went through your data. Can you also tell what is the expected Vaiance for your Identifier 5?
As far as I tried, the value comes as 0. But if you look deeply, it is not 0%. It is something like 0.00070581%
Going by the formula which you have given, for Identifier 5,
Budget = 151644
actuals + Forecast = 127569+14112 = 141681
The Variance Calculation would be something like this
(151644/ 141681) / 151644 = 0.0000070581
When multiplied by 100, it becomes 0.00070581%
I am afraid going by this formula, even excel is going to give the same value.
Click here for the PBIX with the same shown
Clarify if I am missing something here...?
