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
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
Further investigation suggests that it is related to the fact that the data is drawn from SharePoint lists, rather than excel files. I recreated the report using excel lists and it works as required.
Cheers
Steve