cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kennoe Regular Visitor
Regular Visitor

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. 

PowerBI.jpg

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

1 ACCEPTED SOLUTION

Accepted Solutions
kennoe Regular Visitor
Regular Visitor

Re: 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

 

10 REPLIES 10
Thejeswar Senior Member
Senior Member

Re: Calculating value from table values

Hi @kennoe,

It will be easier to assist if you can share the PBIX file with such a scenario present.

 

It will save a lot of time involved with re-creating the scenario from our side to satisfy your requirement

kennoe Regular Visitor
Regular Visitor

Re: Calculating value from table values

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

v-yulgu-msft Super Contributor
Super Contributor

Re: Calculating value from table values

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

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

Re: Calculating value from table values

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

IdentifierIdFunding StatusFunding TypeBudget
5PlannedOPEX136244
5AllocatedOPEX136244
5ReleasedOPEX136244
5ReleasedOPEX15400
9PlannedCAPEX358125
9AllocatedCAPEX358125
9ReleasedCAPEX358125
9PlannedOPEX77948
9AllocatedOPEX77948
9ReleasedOPEX77948
9PlannedOPEX181880
9AllocatedOPEX181880
9ReleasedOPEX181880
9PlannedOPEX103567
9PlannedOPEX517835

 

Table 2 Actuals

IdentifierIdActuals to DateExpense Type
572680OPEX
511334OPEX
543555OPEX
9966OPEX
98574OPEX
9298425CAPEX
919614

OPEX

 

Table 3 - Forecast

TitleIdentifierIdForecast to Complete
Forecast for period 1Aug2018 to 31Aug2018514112
Forecast for period 1Aug2018 to 31Aug20189289893

 

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:

 BudgetActualsForecast
515164412756914112

 

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

Thejeswar Senior Member
Senior Member

Re: Calculating value from table values

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...?

kennoe Regular Visitor
Regular Visitor

Re: 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

 

Highlighted
kennoe Regular Visitor
Regular Visitor

Re: Calculating value from table values

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:

BICapture5.JPGExample data only 

This is the chart for the actual data:

BICapture4.JPG

 

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

 

Thejeswar Senior Member
Senior Member

Re: Calculating value from table values

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

kennoe Regular Visitor
Regular Visitor

Re: Calculating value from table values

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 IdentifierInitiative NamePercent CompleteBudgetActuals to DateForecast to CompleteVariance
C0005101Project 199%$151,644$156,171$3,0045%
C0005101Project 145%  $3,004-100%
C0005101Project 150%  $3,004-100%
C0005101Project 150%  $3,004-100%
C0005101Project 178%  $3,004-100%
C0005101Project 140%  $3,004-100%
C0005101Project 155%  $3,004-100%
C0008007Project 299%  $3,004-100%
C0008007Project 245%  $3,004-100%
C0008007Project 250%  $3,004-100%
C0008007Project 250%  $3,004-100%
C0008007Project 278%  $3,004-100%
C0008007Project 240%  $3,004-100%
C0008007Project 255%  $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

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 374 members 3,788 guests
Please welcome our newest community members: