What-if analysis & simulations
Last updated
Was this helpful?
Reporting & Writeback Matrix Docs
Last updated
Was this helpful?
Everyday business decision-making involves stakeholders asking a lot of ‘what-if’ questions. However, typical business intelligence and reporting tools fail to provide this very capability that decision-makers and analysts need most. As a result, users end up exporting report data to Excel to create their own models for offline analyses.
Inforiver provides two ways to model & simulate outcomes for your ‘what-if’ questions. Let us look at them one by one.
This method involves overwriting existing data in your report directly. However, Business Intelligence & Analytics tools typically do not provide this capability as there is no standard option to persist or write back the modified data.
With Inforiver, you receive the option to not only edit data directly – but also to write back to a database, or export the data to Excel/PDF files.
To simulate outcomes in Inforiver by editing data, you select a specific cell and start typing in a new value. Just as with Excel, a cell editor shows up just over the value.
Type in the new value and press enter to update the value and table totals automatically. There is also an edit icon that appears next to the value indicating that the cell has been edited.
An alternate method to achieve the same outcome is to double-click a specific cell. This opens a formula bar at the top where you can make similar adjustments.
Inforiver also allows you to use expressions with scaled values (e.g., 11250 + 0.5k) and percentages (e.g., 11250 + 10%). You can use this in both the in-cell editor and the formula bar.
You can update even cells/columns that display charts/graphs using the above method. After the update, the charts or graphs render again based on the updated value.
Another method to create projections in Power BI is to use the intuitive simulation capability offered by Inforiver. Compared to the previous method, this approach has the benefit of automatically tracking variances for each record.
To use this feature, select the 2022 Actuals field and click on the ‘Simulate’ icon in the menu. This creates a simulation output field based on the input field (2022 Actuals, in this case).
The side panel provides the following options:
a) Insert as - Visual measure or column
b) Simulation based on - A measure based on which the simulation series is created
c) Variance formatting style - To define whether an increase is good or bad
d) Show slider - To enable/disable the slider. If the slider is disabled, simulation can still be performed by cell editing
e) Value range - By default, each cell can be simulated from -100% to +100% of its value
Once the field has been created, you can click on any cell in this new simulation-enabled field. This will display a small slider icon next to it. Hovering over this icon reveals a slider. Clicking and dragging the slider changes the value in the cell. It also shows a percentage change as you keep moving the slider to the right or to the left. The value & totals get updated once you release the slider.
The Simulate option in the Insert ribbon is available only in the Inforiver Reporting Matrix. For creating simulations in Inforiver Writeback Matrix, refer to 'Scenarios'.
In the example above, we have increased the value for the category Water in the region Pacific by 22%, which results in an overall sales increase of 1%.
When a parent cell is simulated, the change is distributed to the child levels. Simulating Grand Total -> East by 15% increases Beverages and Water for East region by the same percentage.
Multiple cells can be simulated at once. Use Shift/Ctrl + click to select cells and use the slider on any one cell to simulate.
On the simulation slider, you can see three options - lock, edit and delete. Let's look at them one by one.
a) Edit - A simulation percentage can be defined by clicking on the pencil icon.
In the dialog box that opens, type the desired percentage and click 'Apply'.
The value gets updated based on the entered percentage.
b) Lock - Specific cells can be locked from simulations. Locked cells are not affected if their parent values are updated. Click on the 'Lock' icon.
The cell is greyed out to indicate that it is locked from simulations.
On selecting a locked cell, you can see a lock icon. Click on it to unlock the cell.
c) Delete - Simulations can be cleared by clicking on the 'Delete' icon.
Multiple cells can be edited, locked or deleted at the same time by using Shift/Ctrl + Click.
When the simulation slider is disabled, double-click on the cell and edit directly as shown below.
All the variances are in red because the negative variance formatting style has been applied.
Bulk edit feature is also available for simulation measures enabling you to simulate entire row/column categories simultaneously.
Click Bulk Edit from the tool bar.
In the pop-up, select the simulation measure to be edited in bulk, then select the necessary categories and sub-categories from both row and column dimensions. As you select, they are filtered in the report.
Choose the row and column levels where the simulation should be done, as well as the simulation percentage. Click Apply. The results are shown below.
After simulating the values in a measure, you would also need to compare the variance between the original and simulated values. You can use Quick Formulas > Insert variance to instantly calculate the variance for simulated fields.
To learn more about simulations in Power BI, read this blog.
