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
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 |
Forecast | Type | Q1 | Q2 | Q3 | Q4 |
FY19 Budget | Sub-group | $600 |
| ||
FY19 Budget | Sub-group | $600 |
| ||
FY19 Budget | Total | $1200 |
| ||
FY19 Estimate 1 | Sub-group | $750 |
| ||
FY19 Estimate 1 | Sub-group | $500 |
| ||
FY19 Estimate 1 | Total | $1250 |
| ||
FY19 Estimate 2 | Sub-group | $550 |
| ||
FY19 Estimate 2 | Sub-group | $800 |
| ||
FY19 Estimate 2 | Total | $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.
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.
For 3. you should probably unpivot your data so that it looks more like this
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
Help when you know. Ask when you don't!
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.
Help when you know. Ask when you don't!
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
Help when you know. Ask when you don't!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
90 | |
82 | |
76 | |
64 |
User | Count |
---|---|
144 | |
109 | |
108 | |
100 | |
96 |