Formatting Complex Variance calculation

[tobiasmcbride]

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.

[v-juanli-msft]

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]

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]

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

[kentyler]

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.

[kentyler]

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