Budget analysis in Power BI
1. Budget analysis in Power BI
Welcome back! In this screencast, we will learn how to add the budget and forecast for each line item to your balance sheet and create variance measures. Currently your data only has actuals by month. In order to analyze actuals versus Budget and forecast we are going to bring in our budget and forecast table. We do this by going to our Query Editor and on the left side we will see a fact table called budget and forecast. When we right click on each table we see that the tables have not been loaded to the model. Enable the table so that it is brought into our model. We then select close and apply our query editor and see that the table has been loaded into our model. You can see this in the Data pane. In order to bring this table into our report, we go to model view, create a new layout called Budget, and drag tables “Date”, “Budget”, “Ledger Mapping” and “Balance Sheet Layout” to the model. Similar to our Actuals Layout, we create relationships between the tables. Click the "New Layout" tab to add a new model view. We connect the date table to the budget table by using the Date column. Connect the Budget table to Ledger Mapping using the Balance Sheet Line Items column. Then lastly we connect our Ledger Mapping table to the Balance Sheet Layout using the primary key column. We will do a similar process for forecast. Go to model view, create a new layout called Forecast, and drag tables “Date”, “Forecast”, “Ledger Mapping” and “Balance Sheet Layout” to the model. Now we return to our visualization page to start bringing in our budget and forecast numbers. First lets remove Month Name from our columns and add year from our Date table. You will see our months have disappeared and we are only visualizing years. Next we go to our model Measures and drag Budget and Forecast under values. These measures are created for you in the data pane. We then format this column by going under the visualizations pane, format your visual, cell elements and select budget from the dropdown menu. Next turn on Background color and font color, click the function icon to get additional options. First on the background we select, Field Value under format style. Then under “What fields should we base this one?” we select Balance sheet layout First HexCodes, Hex Code Background Then select Values and totals under apply to and First under summarization. We then repeat this process for forecast. Then on Font we select, Field Value under format style. Then under What fields should we base this one? we select the Balance Sheet Layout table and then select “First HexCodes, Hex Code Text” Then select Values and totals under apply to and First under summarization Now we can see our balance sheet updated with our budget and forecast information. Last but not least we are going to calculate the Budget and Forecast Variance. Right-click on the Model Measures table and create a measure called “Variance of Actuals vs Budget, #” We use the Logic “if” function. Which tells us If actuals are greater than 0, then find actuals - budget where positive values > 0. And If actuals are less than 0, then - (actuals - budget) where positive values < 0. Your equation should look like this: Variance of Actuals vs Budget, # = var result = if ([Actuals]>0, [Actuals]-[Budget], -([Actuals]-[Budget])) return result” You will notice that you are given the # of Actuals vs. Budget. Now it is your turn.2. Let's practice!
Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.