Dynamic Variance Analysis with Power BI - Different Forecast Versions Comparison

When I was first started my journey with Power BI, I always struggled to create visualization for showing the variances between different budget/forecast versions. Things are always done manually in Excel as I'm only able to total up the different versions and manually substract them in a spreadsheet. I'm able to find videos and tutorials about actuals comparing against budget and previous years, but not something about comparing different forecast versions and where you can change dynamically

After some research and self-studying, I am finally able to create a dashboard specifically for comparing different forecast versions against one another dynamically with Power BI.

In this video, I will share the steps to setup your data model from scratch so that you can achieve similar outcome with Power BI and you can compare all the different forecast versions you have in the archive easily.

00:00 Introduction
00:17 Data Model Example
01:30 Step 1 - Create an index table containing all unique forecast versions
02:48 Step 2 - Create a copy of forecast versions table to be used as the comparison
03:08 Step 3 - Create slicers for users selection
04:13 Step 4 - Create all measures required to calculate variances and using matrix visual for data validation
06:17 Setup Bar Chart to visualize variance
07:07 Setup Waterfall Chart to visualize variance
07:35 Setup Line Chart for trend analysis and visualization
08:22 Trailer for future videos - Comparison against multiple versions all in a single table
08:34 Thank you and see you!!

PBIX file used in the video:

MY FAVOURITE GEAR

Disclaimer:
Do note that this is the way I built my data model and it is by no means the only way or the most efficient way to do it. If you have better and more efficient way to achieve the outcome, feel free to comment and let me know about it.

#PowerBI #VarianceAnalysis #ComparisonReport #DynamicVarianceAnalysis #Variance Analysis

🤝 BE MY FRIEND:

🙎🏻‍♂️ WHO AM I:
I'm Joel, an analyst working in Singapore. I make videos about technology, automation and productivity.

📧 GET IN TOUCH:

[TheHermit_md]

Excellent - helped me solve a problem I have been struggling with for quite a while. Thanks!

[dcpowered]

Thanks a lot, Joel! This is extremely useful. Please keep posting new videos!

[baranip]

Hey Joel, was looking various videos and finally landed with your video. Nice video and it helped me to fix my report. Thanks a ton!

[malililaverie]

Excellent video Joel, just what I was looking for

[arpatil]

Hey Joel, thanks for this helpful tutorial. Excellent indeed.

[cestlavie]

You are awesome! Cant thank you enough! This is very helpful. This is exactly what I was looking for.

[Sachin_Kajave]

Hey Joel, thank a lot .. God bless you

[wjcstella]

Thanks for sharing, this is exactly what i am trying to do. Learnt a lot from this video.

[rachelzhou]

Hello Joel ! Thank you so much for the content, i have been searching for a while for this comparison and you did an amazing job to explained, thank you !
I'm wondering if you will do the Different comparison with multiple version video ? looking forward to that video .

[gleneshelby]

Hi Joel, This was really useful. Do you know if there's a way maybe through a tool tip to sort my waterfall's in the same order each time, eg. comparator left side and selected version always on the right?

[franjaeger]

Hi Joel, This video was excellent and helped me a lot. I have a question, because now I have to create a DSO tendencies chart, so I would like to select many values, as it was for forecast but now I need have it in X axis. Do you know how can I do that?

[mathieud.]

Hey Joel thanks a lot for that tutorial, very helpful. The only issue i'm still facing is the sorting of the waterfall chart. On PBDashboard you can sort ascending or descending to change the starting point of the graph, but on the online version, that sort option is not available and depending on the comparator chosen, the graph is displayed in the wrong sense. If someone succeeded to solve that, please let me know ! Cheers

[alessandrovavala]

Hello Joel! Super useful video, thank you!
Do you know if it is there a way to rename the columns name with the value selected in the two slicer? For example, in case the selected version is May RE instead the column having the header as “Selected Sales Revenue” it has “May RE Sales revenue”
Thank you again!

[bijijohn]

Hi Joel, thank you so much for this. It is very useful. I am looking to see the variance by month as well as total while comparing 2 forecast submissions. How can we do it? Do you have any suggestion?

[javedhassansheik]

Hello Joel! a very good educational video I had a small requirement how do you create the measure to tell how many of the food items having variance greater than 1

[DineshKumar-rtrz]

Hey Joel, lets say you want to have the header of the selected sales revenue and comparator sales revenue to be dynamic (based on the user selected value in both the slicers). If user selected JAN RE in selected sales revenue slicer and MAR RE in comparator sales revenue then the columns headers should be like JAN RE and MAR RE
What should we do ?

[jayanthipremraj]

Hi - Thanks, this is really useful. can you pls tell me how to change the column header based on the selected value that is month whether Apr RE and May RE?

[adityashekharmantripragada]

Hey Joel, very helpful tutorial!
What if one wants to analyse variances between multiple selections?

[carlosgomez-dfcg]

Great video, it helped me a lot. Only i have a question, i need show the data in a matrix but i have issues to change dynamically the column header with the values selected to compare. I only can show the variable name. Is it possible?.

[hishamelabd]

First of All, Thanks a lot

Then wanna ask, how to create a dynamic header name for those columns ?