Dynamic Variance Analysis with Power BI - Different Forecast Versions Comparison

Рет қаралды 20,033

Күн бұрын

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:
drive.google.com/file/d/1EXSp...
MY FAVOURITE GEAR
💻 My Personal Laptop, Legion 5 Pro - amzn.to/3nZrn1I
✈️ My Travel Companion, Macbook Air M1 - amzn.to/43jLLuE
📷 My Camera Gear, Sony ZV-E10 - amzn.to/40T7uIj
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:
🐦 Twitter - / joelting92
🏢 Linkedin - / joel-ting
🙎🏻‍♂️ WHO AM I:
I'm Joel, an analyst working in Singapore. I make videos about technology, automation and productivity.
📧 GET IN TOUCH:
If you'd like to talk, I would love to hear from you. Email me directly at joelting92@gmail.com would be the quickest way to get a response. I will try my best to reply to your email as soon as possible.

Пікірлер: 44

@dcpowered Жыл бұрын

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

@malililaverie7977 8 ай бұрын

Excellent video Joel, just what I was looking for

@baranip1331 Жыл бұрын

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!

@JoelTing Жыл бұрын

Hi Barani, glad it helped! If you are looking to compare multiple versions at the same time, feel free to check out my latest video on this. It's a follow up video for this.

@cestlavie6232 9 ай бұрын

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

@arpatil26 Жыл бұрын

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

@wjcstella 8 ай бұрын

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

@JoelTing 8 ай бұрын

Glad it was helpful!

@Sachin_Kajave Жыл бұрын

Hey Joel, thank a lot .. God bless you

@karinahulka9980 Жыл бұрын

thanks for the video!

@championlehlohonolontulini9957 7 ай бұрын

It’s best I watched thus far

@user-tj6em4vf1z 5 ай бұрын

Excellent!

@Gogobs728ssskenda 2 жыл бұрын

Thank you for the tutorial!

@JoelTing 2 жыл бұрын

Hi Asher Chen, nice to meet you!

@rachelzhou916 2 жыл бұрын

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 .

@JoelTing 2 жыл бұрын

Hi Rachel. Thanks for the kind word! Apologies for for the late reply. For a comparison with multiple version, I'll try to work in out within 2 weeks time. Meanwhile, if you want to understand earlier to implement it in your project, feel free to reach out to me privately via email. I'll be happy to help.

@rachelzhou916 2 жыл бұрын

@@JoelTing Thank you So much Joel for replying to me . If not too much trouble i would like to book some of your time , I will send you email about the appointment , again thank you !

@gleneshelby1417 Жыл бұрын

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?

@JoelTing Жыл бұрын

Hi there. For now, I'm not able to do that. The closest I can get is making sure the order follows based on my sorting using index. That needs to be pre-determined (ie. May RE will always be placed at the right when comparing against Mar RE and will always be place at the left when comparing against Jun RE).

@franjaeger1997 Жыл бұрын

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?

@JoelTing Жыл бұрын

Hi Francisco Jaeger. Glad to hear that! For your question, I don't really understand what you are trying to achieve. Would you mind to elaborate further? You can consider to email me since you will not be able to attach screenshot here.

@kaas347 Жыл бұрын

Thanks!

@kaas347 Жыл бұрын

The subtle and magical key to this is creating a DISCONNECTED table with a list of each comparison input, which is genius and I never thought to do it!!! I've been using Calculation Groups to achieve this with some super intense measures and Calculation Precedences that are so hard to reconcile and debug. THANK YOU!!!!!!

@JoelTing Жыл бұрын

Appreciate your kind words and super thanks! Glad that I'm able to help! 🙂

@alessandrovavala3536 Ай бұрын

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!

@JoelTing Ай бұрын

Hi there. For the column names, it's possible if you don't have a column to compare the variances. I have an example (may differ slightly for your use case) in a different video where you can use VALUES function to calculate the total of selected and comparator and put the version in the column field of matrix visual. kzfaq.info/get/bejne/rsqioJh3vcvZmmg.html

@jayanthipremraj8072 Жыл бұрын

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?

@JoelTing Жыл бұрын

Hi there. You can have a measure that adds up the selected and comparator variable. Then, just use that measure in a table and set the column as the Forecast version column. As you select your versions, they should just change accordingly. However, it only work nicely if you have only 1 measure. If you are calculating variances and % variances, it can get messy as it will apply to the selected measure as well. You can check out the video below, where the multiple comparators can be selected and the column header is changed based on what is being selected. kzfaq.info/get/bejne/rsqioJh3vcvZmmg.html

@carlosgomez-df9cg Жыл бұрын

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

@JoelTing Жыл бұрын

Hi there. I tried to do something like this before. You can do that by having another new measure where it sums up the selected and comparator measure. Then use that measure in the table and for the column in the visual, use a lookup field where both the selected and comparator tables are connected. That way, when you change, it will show the column header based on what you have selected. I have a video which covers something similar, kzfaq.info/get/bejne/rsqioJh3vcvZmmg.html&t&ab_channel=JoelTing The drawback of this way would be that you will not be able to display the variance in a nice way if you combine both selected and comparator in the same matrix visual.

@bijijohn7983 2 ай бұрын

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?

@JoelTing 2 ай бұрын

Hi there, are you looking to build it in a waterfall visual as well? I believe you can bring in the date table months and it will work just like how the categories are working.

@mathieud.1147 2 жыл бұрын

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

@JoelTing 2 жыл бұрын

Hey Mathieu! Glad that you find the video helpful. I checked from my end, it seems that I'm able to sort it based on certain criteria even on the online version (eg. Based on the month the forecast being prepared). Let's discuss further on this.

@javedhassansheik903 5 ай бұрын

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

@JoelTing 4 ай бұрын

Hi there, I would aggregate the measure 1st, then do a row count to determine how many food item is having variances greater than 1. Perhaps something like the following: VAR AggregatedSales = SUMMARIZE('SalesData', 'SalesData'[FoodItem], "TotalSales", SUM('SalesData'[Sales])) VAR VarianceCalculation = ADDCOLUMNS(AggregatedSales, "SalesVariance", [YourVarianceCalculationHere] /* Replace with actual variance calculation based on TotalSales */) RETURN COUNTROWS( FILTER( VarianceCalculation, [SalesVariance] > 1 ) )

@adityashekharmantripragada4386 9 ай бұрын

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

@JoelTing 9 ай бұрын

Hi there. You can try to refer to this link for multiple selections. kzfaq.info/get/bejne/rsqioJh3vcvZmmg.html

@DineshKumar-rt8rz 12 күн бұрын

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 ?

@JoelTing 11 күн бұрын

Hi there, to create dynamic column headers based on user selections in both slicers, you can have a measure that adds selected and comparator total together. Then create a version table that has relationships with both the selected and comparator tables. Use this version table as a column in your visual. This way, you will see the column header changes according to the users' selection. However, it's important to note that this method has a limitation: you won't be able to visualize variance in the same table, as the version is now being used as a column.

@satishkarkare1814 5 ай бұрын

hi mate when ur selecting the selected version that field exMAY RE should come on the matrix please help me out

@JoelTing 5 ай бұрын

Hi there, sry, I could not understand your question. Can you clarify further?

@hishamelabd4429 Ай бұрын

First of All , Thanks a lot Then wanna ask, how to create a dynamic header name for those columns ?

@JoelTing 28 күн бұрын

Hi there, as of now, there does not seems to be a way to have the columns change dynamically based on the measure that is getting displayed. Workaround would be having the "Version" as column in the matrix, then the column name will change according to what you have selected but you will not be able to visualize the variance nicely.