Forum Discussion
Calculating value from table values
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
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...?
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
While the solution proposed was correct, I have not been able to make it work in real life. My actual data is more complex than the example used to devise the solution and I think there is an issue with the relationships between the tables that is causing the problem.
Here is the relationship chart for the example data:
Example data only
This is the chart for the actual data:
The extra table is the only real difference between the charts and it means I cannot have the same relationships between the tables.
Any help appreciated.
Steve
Hi kennoe,
Try this approach. Not sure how this will suit for your data.
If possible can you remove the join between the Actual Expense and Initiative Register. That should be aloowing you to make the join beween Actual Expense and Forecast as active. My guess is it will not affect the relationship between Actual Expense and Initiative Register as these tables will continue to be related through Forecast table
Note: Keep the join between them Bi-directional, so that the Initiative Register table continues to be in the model and respond for any changes that happen with the Forecast Table while slicing and Dicing the data
Thanks, but that didn't work. My data may be more complex than the example suggests. Working on a solution, I have got to this stage where I can make the formula work used as a measure, but it throws up a large number of extra rows with incomplete data. Refer extract shown below.
Initiative Identifier Initiative Name Percent Complete Budget Actuals to Date Forecast to Complete Variance C0005101 Project 1 99% $151,644 $156,171 $3,004 5% C0005101 Project 1 45% $3,004 -100% C0005101 Project 1 50% $3,004 -100% C0005101 Project 1 50% $3,004 -100% C0005101 Project 1 78% $3,004 -100% C0005101 Project 1 40% $3,004 -100% C0005101 Project 1 55% $3,004 -100% C0008007 Project 2 99% $3,004 -100% C0008007 Project 2 45% $3,004 -100% C0008007 Project 2 50% $3,004 -100% C0008007 Project 2 50% $3,004 -100% C0008007 Project 2 78% $3,004 -100% C0008007 Project 2 40% $3,004 -100% C0008007 Project 2 55% $3,004 -100% This pattern is repeated for each project.
Interestingly, if the same formula is included as a column, the above pattern is not repeated, but the calculation is incorrect. It is -100% for each project.
My formula is:
Variance = calculate(divide(sum('Actual Expense'[Expense Amount])+sum('Forecast Expense'[Forecast Amount]),sum('Initiative Funding'[Amount]))-value(1)).
This seems to be some filtering issue.
Thanks
Steve