What-If? Understanding Uncertainty with Power BI
Introduction to Power BI
Power BI is Microsoft’s offering in the crowded business intelligence space. While many of its competitors are well established (Tableau, Qlik, etc.), Power BI holds its own in terms of capabilities, and has the added benefit of being fully integrated within Microsoft’s platform. In fact, in Gartner’s 2020 rankings of analytics and business intelligence platforms, Microsoft had pulled ahead into a clear lead:
As described by Gartner, the chief appeal of Power BI is its ubiquity. Gartner states, “many large organizations already own Power BI through enterprise software agreements,” and the familiarity many users have with other Microsoft products (e.g. Excel) leads to a short learning curve with Power BI.
When it comes to core functionality, Power BI is on par with the leaders. While its visuals might not be as polished as Tableau’s, they are intuitive and appealing. Similarly, while its data transformation capability might not be as robust as Qlik Sense’s, it has an ETL capability and can therefore deliver on the requirements of the vast majority of analytics projects .
Value of What-If Analysis and Modeling
The advantages of moving away from static Excel reports toward business intelligence tools like Power BI that can dynamically visualize organizational data are, by now, well understood and have been extensively documented elsewhere . What many organizations are seeking is how to take the next step and get more insight out of the reports and dashboards that these applications provide.
What-If analysis can deliver exactly that. Often, even organizations with mature data visualization capabilities keep their decision modeling separate from their dynamic reporting. They tend to use tools like Power BI to create interactive reporting that displays data trends that are easy to filter and drill into, but to model out different potential scenarios they still resort to classic modeling in Excel.
With Power BI, incorporating such What-If analysis into existing reporting is quite straightforward. Modeling capabilities can be married with dynamic visuals, giving the end user even more power and flexibility when viewing their organization’s data. In this fashion, Power BI can help you not only answer the question of “What happened?” but also answer the question “What could happen?”
How to Create a What-If Slicer in Power BI
So how exactly does it work in Power BI? Fortunately, it’s almost as simple as clicking a button. Specifically, the “New parameter” button on the Modeling tab, helpfully labeled with “What if.”
Clicking this button brings up a window where you can specify the attributes of the parameter. Along with a name, you can provide the data range of available options to select. Choosing this data range is the key to how the parameter works. The end user will still view the parameter as a truly open-ended What-If question, but Power BI will treat their selection as if they simply filtered the underlying mini dataset of values. Indeed, the input window even has a checkbox that lets you add a slicer to the page upon creating the What-If parameter. This lets the user choose the parameter’s value using Power BI’s traditional slicer visual.
With this slicer, the user can either enter in a value in the free response box or drag the slicer to incrementally change the parameter’s value. The increment you specify in the setup window will determine the sensitivity of the slider, so if you want users to choose between a wide range of data, use a larger increment.
For the developer, the selected parameter value can then be incorporated into calculations so that metrics change dynamically along with the user’s selection. As part of the parameter creation, Power BI automatically creates a table with the data range of values along with a measure that returns the user’s selected value (if you’re familiar with DAX, it does this by leveraging DAX’s “SELECTEDVALUE” function).
Since it’s a measure, it can be easily added into any existing DAX calculation in whatever way makes the most sense for that parameter. For example, if the parameter represents a percent change then it might multiply an existing calculation. If it represents a nominal change, it might just be added to an existing measure.
Real World Examples of What-If Analysis
At Kenway, we have found What-If parameters immensely helpful with the forecasting process. Having built out our internal reporting suite in dynamic Power BI dashboards, we can rapidly pivot from viewing best case and worst-case scenarios when planning for the next 6-12 months.
For instance, our revenue forecasting is built bottom-up from our internal data based on projects we have already won and entered into our ERP system. While this is very accurate for the near term, it describes a smaller portion of reality the further into the future we forecast since a larger percentage of those projects haven’t been won yet and therefore do not appear in the ERP system. To better understand this component, we can layer in data from our CRM pipeline, but this brings a certain amount of uncertainty with it since we must estimate how likely a project is to be won .
Enter the What-If parameter. Though we have a good idea of how many projects we might win based on historical trends, we can use What-If parameters to explore how our revenue forecasts change with different future project mixes.
For even more flexibility, we can use multiple What-If parameters at once. In this example, number of projects, hours per project, and rate per project are all configurable parameters. The product of these three values is what gets added to the revenue forecast but, because of the Power BI functionality, there’s no limit to how intricate you can get with the calculations. In the below video, see how changing all three of these values alters the overall revenue forecast.
This example shows how an organization can utilize What-If parameters to forecast at the macro level, and it’s not hard to imagine how the same concept could apply at a more micro level on an individual project basis. A common challenge we face as a consulting firm is optimally staffing our projects. When a project is won, we generally know how many hours it will take and what bill rate the client will pay. We must then decide how to staff the project so the work is completed on time and under budget. By setting up What-If parameters based on hours each resource on a project works, we can combine our internal data with these results to fine tune the best staffing plan to move forward.
In the video below, see how a project planner can swap resources, hours per resource, and project bill rate to rapidly fine tune the staffing mix for a project.
How Kenway Can Help
Have you been asking more of your data? Do you ever look at reports or dashboards and wonder, “What if….?” If so, Kenway can help. For all things data, check out Kenway’s Information Insight capability on our website to learn more, or email email@example.com.