Analyze in Excel or Inforiver for Power BI – for Ad-hoc Analysis?

Like pretty much any piece of software, Power BI has multiple ways to solve any given problem. In this article today, I'm going to explain why you might want to use the Inforiver custom visual instead of using the standard Analyse in Excel capability that comes with Power BI.

Why Does Analyze in Excel Even Exist?

There's an industry joke that you may already have heard; it goes something like this.

Question: What are the three most common buttons that come with any Business Intelligence tool?

Answer: OK, Cancel, and Export to Excel

I think it’s funny, but it’s also true. This highlights a problem that all BI software tools face, and that is that those that use BI software are used to working in Microsoft Excel.  They feel very comfortable with Excel, and they often don't invest the time to learn how to use new tools.  Analyze in Excel is Microsoft’s attempt to short circuit the need for the “Export to Excel” button so that people can have the best of both worlds.  Once you export your data to Excel, you have created a “copy” of the data and it will no longer be in sync.  The Analyze in Excel feature allows you to create a live connection to the data but still allow you to use the familiar Microsoft Excel (typically Pivot Tables).

Reasons People Use Analyze in Excel

There are many reasons people might choose to use Analyze in Excel, including:

• They like the familiar Excel Pivot Table experience
• It provides the ability to create a sharable document (xlsx) that can be emailed to someone that does not have a Power BI Pro license (note, the pivot table will not be interactive in this use case).
• To more easily produce multi page printable reports than is possible in standard Power BI (unless you use paginated reporting).
• The lack of pixel perfect column width control in a standard Power BI matrix.
• Expand & collapse along column hierarchies is still missing in Power BI despite being the #1 voted idea place item for the last 5+ years

Inforiver Provides an Excel Like Experience Live in Power BI

While there is no doubt that the reasons people use Analyze in Excel have some merit, once you leave the Power BI environment to leverage Analyze in Excel you walk away from many other useful features that are not available in Analyze in Excel.  The Inforiver visual solves this problem by providing a familiar visual experience that is similar to what you get in Excel pivot tables without the need to leave the Power BI workspace at all.  The benefits of using Inforiver as an alternative to Analyze in Excel, include:

• Being able to leverage the Power BI service (PowerBI.com) as the delivery mechanism for all reporting.
• All users have the same live copy of the data and report without the need alternative access paths to the bespoke Analyze in Excel reports.
• Supports integration with other standard Power BI features that cannot be leveraged when using Analyze in Excel, such as:
  • Cross filtering of visuals on a single report page
  • Drill through reports
  • Custom Tool Tips
  • Subscriptions to the report and visuals. [Using AppSource PBI Certified Version]
• You get the ability to control the exact width of columns and rows just like you can with Analyze in Excel. [Manage Columns]
• Expand/collapse hierarchies along both rows and columns like traditional pivot tables as well as support for stepped, outline and classic pivot table layouts inside Power BI

Not All Companies Want Excel to be Part of the Solution

It is not my role to pass judgement as to whether a company should or should not use Excel as part of its enterprise reporting solution.  What I do want to point out, however, is that some (many?) companies simply don’t want to have Excel as part of the solution.  These companies want their staff to change the way they think about reporting and move them fully into the enterprise reporting space as part of their digital transformation initiatives.  Arguably, keeping Analyze in Excel as part of the reporting solution goes counter to this objective.  Inforiver closes the gaps between the structural reporting limitations of the Power BI standard Matrix and the full flexibility of Excel, eliminating this barrier to moving everything to enterprise reporting if desired. 

Inforiver Has Capabilities that are not Available in Analyze in Excel

In addition to all the points covered above, there are plenty of benefits that Inforiver offers over and above the standard Analyze in Excel experience without having to walk away from the familiar Pivot Table experience. Here are a few of the more important added benefits.

Directly edit cells

It is possible to directly edit any cell inside the Inforiver matrix regardless of if it currently contains a value or not.  You can manually override any number that you need to change, or indeed write a new formula directly in the cell.  This is not possible in a regular pivot table.

Insert Rows and Columns Directly into the Inforiver Matrix

Sometimes you need to add something into a pivot table that simply doesn’t exist in the underlying data, such as when there is a planned new product launch.  Using Inforiver, it is easy to insert a new row (or column) to add in something that is missing.  Note how the new budget entry rolls up to the total budget, too.

Access Advanced Visualisation Techniques

Inforiver has many in built visualisation charts that are simply not possible in standard Excel.  This includes the automatic creation of special chart visuals such as the bullet chart shown below (to show just one option).

Inforiver also fully supports the IBCS reporting standards with out of the box templates.

Auto Calculation of Variances

In standard Power BI and when using Analyze in Excel, it is necessary to write every single measure possibly needed to build out the required reports.  Inforiver has the ability to automatically generate standard variance calculation (actual and %) against items such as Actual, Budget and Prior Year.  This is super helpful when you have many baseline measures that all have to be reported on at different times.

Against Items such as prior year, budget, forecast

Summary

There are many benefits to using Inforiver over the Analyze in Excel experience. Inforiver is as good as Analyze in Excel for the common reasons people default to leave the Power BI environment and yet it has so much more to offer over and above the basics.

The latest announcement to be able to directly procure Inforiver directly from Microsoft is a welcome news for all Power BI Customers.

This blog is written by Matt Allington (Self Service BI Expert + Microsoft MVP) , Excelerator BI 

Sign up for a FREE trial in less than a minute!