1. Sensitivity analysis in Power BI
Welcome back. In this screencast, we will review how to make a sensitivity table with what-if parameters. In this case, we want to show the sensitivity of profit margin between revenue and cost of goods sold, or COGS for short.
We'll start by creating what-if parameters for the independent variables, revenue, and costs of goods sold. These parameters will represent a percent change in the underlying values.
We'll go to modeling, then new parameter. We'll name this change in revenue. We want to show a range between negative 25 percent and positive 25 percent, at 12-and-a-half percent increments. Because Power BI cannot take percentages in this context, we need to make the parameter a decimal number, so the range will be between negative point 25 and point 25, at point 125 increments. We do not want to add a slicer because we don't need it.
Great. Now we'll make a new measure, called Revenue Scenario, and we'll multiply the SUMX of revenue by one plus the change in revenue value.
If we put this on a table, we can see all the outcomes, and we'll make sure to format the parameter as a percent.
Now we'll make the second parameter for change in cost of goods sold, following the same steps as before.
And we'll make a measure, called cost of goods sold scenario, where we'll multiply the SUMX of cost of goods sold by one plus the change in cost of goods sold value.
We’ll put this on a table to see all the outcomes, and we'll make sure to format the parameter as a percent as well. We could also use a funnel chart to display this, as it gives a nice visual, and we can turn off the conversion rate.
Finally, we can combine these two parameters to create a sensitivity table. Recall that profit margin is net income divided by revenue, and profit is simply revenue minus expenses.
So we'll create a new measure called profit margin sensitivity, using the DIVIDE function. We'll calculate net income in the numerator, which is Revenue Scenario minus cost of goods sold scenario. Then Revenue Scenario will go in the denominator.
Now we can put this in a matrix with Change in cost of goods sold in the Rows and Change in Revenue in the columns, and Profit Margin Sensitivity in the values.
Let’s clean this up a bit and delete the subtotals by toggling them off in the formatting pane.
Awesome! This looks so good. Now that you’ve seen it, it’s your turn to give it a try.
2. Let's practice!