Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
tobiasmcbride
Helper III
Helper III

Formatting Complex Variance calculation

Hi,

 

Am trying to do what seems to be at first sight an easy variance calculation but would like some help with it. I have actuals and budgeted figures and want to be able to dynamically calculate a variance. By dynamic I mean I'd like it to change depending on which budget is selected and also be done across the quarters. 

 

The data is formatted like so in Excel:

 WORLD
ForecastTypeQ1Q2Q3

Q4

FY19 BudgetSub-group$600  

 

FY19 BudgetSub-group$600  

 

FY19 BudgetTotal$1200  

 

FY19 Estimate 1Sub-group$750  

 

FY19 Estimate 1Sub-group$500  

 

FY19 Estimate 1Total$1250  

 

FY19 Estimate 2Sub-group$550  

 

FY19 Estimate 2Sub-group$800  

 

FY19 Estimate 2Total$1350   

 

You will notice two aspects needing help with:

1. Firstly there is a row at the very top which is merged called World - this is because the data stretches across multiple sectors but for simplicity we narrowed it down to USA, ROW, & World. I need to find a way to include this categorisation in PowerBI to make it easy. The data above is only shown for World but there is another part of the table that is for America and RoW (Rest of World) - is it easier to just split this into 3 tables? 

 

2. I also need to find a way to insert whether the values in the quarter columns were actuals or forecast but not sure how to do this without screwing up the formatting for the columns being currency/numbers. I.e. need somewhere to say FY19 Estimate 1 is currently actuals but in Estimate 2 Q2 is forecasted for instance (ignoring current dates)

 

3. I then need to be able to calculate a variance. This would be the total of the latest estimate against the FY19 Budget 'total' I need to be able to calculate this on a Q-by-Q basis and also filter which latest estimate I am calculating the variance using (i.e. it would be FY19 Estimate 2 total minus FY19 Budget and taking % difference of that across quarters but also need ability for variance to be calculated using, for instance, FY19 Estimate 1).

 

Hope this makes sense. This is the raw data that we have and the way it is formatted but any assistance using either Excel or PowerBI (preferably latter) to get nice data formatting and calculations would be fantastic.

5 REPLIES 5
v-juanli-msft
Community Support
Community Support

Hi @tobiasmcbride

Is this problem sloved? 
If it is sloved, could you kindly accept it as a solution to close this case and help the other members find it more quickly?
If not, please feel free to let me know.
 
Best Regards
Maggie
Anonymous
Not applicable

In most cases, Power BI Desktop automatically calculates and returns values according to the types of fields and aggregations you choose. However, in some cases you might want to create your own measures to perform more complex, unique calculations.

kentyler
Solution Sage
Solution Sage

For 3. you should probably unpivot your data so that it looks more like this

estimate.PNG

You can then write some DAX that, for a given row, if its an estimate, looks up the matching budget and calculates the variance.

If you add a calendar table that would be linked to the quarter column, you could then use that to do totals at the yearly level.

Both the DAX and the links to the calendar table get a little complicated. If you'd like more help email me and we could do an online session and work thru them. ken@8thfold.com





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


kentyler
Solution Sage
Solution Sage

For question number 2 you would need to add either a calculated column or a measure that returns "actual" or "forecast". The logic for figuring out which to return would be more or less the same you are using to do the calculation of the value, you would just return the descriptive term instead of the value.





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


kentyler
Solution Sage
Solution Sage

to your question 1.

you can probably use a parameter query

https://www.biinsight.com/power-bi-desktop-query-parameters-part-1/

to switch your reports from usa, row and world

 





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

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

October NL Carousel

Fabric Community Update - October 2024

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