With the right tools, incorporating 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. Tools such as Power BI, Microsoft Excel or Google Sheets can help your team not only answer the question of “What happened?” but also answer the question “What could happen?”
In the everchanging environment that businesses are being pushed to operate in, having these answers is crucial. Read on to learn more about how you can begin to understand uncertainty with 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 Gartner’s 2022 rankings of analytics and business intelligence platforms, Microsoft continues to be in the position that is furthest along for Completeness of Vision and the highest in the Ability to Execute within the Leaders quadrant.
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.
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. This data is based on deals we have already won and entered into our ERP system. While this is very accurate for the near term, forecasting further into the future can be difficult as a larger percentage of those deals 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 uncertainty with it since we must estimate how likely a project is to be won.
Read more about we’ve helped our clients achieve success with Power BI in this case study.
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.
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.
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 Kenway faces 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, if possible, 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.
The advantages of moving away from static reports toward business intelligence tools, like Power BI, extensively documented elsewhere. What many organizations are seeking is action - how do they take the next step and get more insight out of the reports and dashboards that these applications provide to drive actual change?
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.
There are many tools that are capable of creating these What If scenarios including tools, you may already have such as Microsoft Excel and Google Sheets. Below, we’ll go into detail on how to use a particular tool, Power BI, in creating What If Scenarios.
So how exactly does a What-If analysis 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.”
1. Leverage the “New parameter” tool in excel:
2. Specify Attributes of the Parameter:
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.
3. Create Calculations
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.
4. Continuing to Optimize
Leveraging the actions in steps 1-3 will allow you to reach insights quicker through the use of custom parameters that fit your company’s unique needs. Continuing to optimize from here to ensure your parameters stay up-to-date will help your team realize continued success.
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. If you want to know how it would work in your organization, read more here or reach out to us today.
