Excel reports that combine data from Power Pivot and Power BI

Let's say that you have some budget data in an Excel Data Model. In Power BI (PBI) the corresponding actual historical data has been published by your finance department. You want to conduct some performance analysis between the two data sets.

As you may know, a 2016 enhancement to PBI allows a Data Model published to PBI to be analyzed in Excel 2016 using the aptly-named Analyze in Excel menu option. So how can you mash up your local budget data with your PBI actuals? You can create two PivotTables, one from each source but each has its own set of row labels so it is difficult to get Actual and Budget in adjacent columns with the correct alignment.

Using Excel cube functions you can achieve the required layout and alignment, and use one common slicer to filter data from both data sets as you can see in the visual below. Stay tuned for a video tutorial on how this can be done.

Combining Power BI and Data Model data

Same-store sales and comparison to prior year

One way to build your Power Pivot Data Model for easy comparison of current year results to the same period in the prior year. Also explains a method to track year-to-year %age change of same-store sales.

Same-store sales and prior year

What did Power BI do with my hierarchies?

You may have created a Power Pivot Data Model that included a hierarchy. For example in your Date (or similarly-named) Lookup table you created a hierarchy called Fiscal, to make navigation of the Date table easier, for Years - Quarters - Months.

Now you have imported your Data Model into Power BI and your Fiscal hierarchy has disappeared. Power BI does not currently support hierarchies so you will not see it there. However if you connect Excel to your Power BI model using the Analyze in Excel feature, the Fiscal hierarchy will be visible in the Excel PivotTable Field List, despite the fact Power BI is your data source.

Handling cumulative GL account balances in Power Pivot

How to handle imported cumulative monthly accounting balances

Cumulative and monthly balances

Business Modeling and Planning with PowerPivot

This multi-part section describes how you can add assumptions and modeling capabilities to you Power Pivot Data Model.

Assumptions table in Data Model

Excel and Power Pivot for accountants and finance managers

Excel, Power Pivot, and cube formula reporting can be enormously powerful tools in the hands of accountants and finance managers, Click here to learn more.

For accounts and finance managers

Excel 2013/2016 Data Model

This video will help you learn:

  • if your version of Excel 2013 has it
  • some advantages the Data Model offers

Excel Data Model

Slicing and dicing text values in a PivotTable

From time to time you may want to retrieve text (as opposed to numeric) values into a PivotTable to slice-dice-pivot the text. Or you want to get properties/attributes of a dimension member in a PowerPivot model using Excel cube functions. Here is one solution.

Retrieving text in a PivotTable

Small Business Intelligence

In addition to providing self-service, personal BI in larger organizations Power Pivot for Excel is an inexpensive BI tool for finance professionals who manage the accounting and reporting for small businesses using QuickBooks. The section on Small Business Intelligence will includes a growing collection of articles on leveraging QuickBooks data using Power Pivot for Excel

Using QuickBooks classes in Power Pivot

Building a dashboard with Power Pivot

Building Actual vs. Budget comparisons into your Excel dashboards

The frequent requirement to compare actual performance to budget can be difficult to achieve with a Pivot Table because Actual and Budget data often come from two different sources. With PowerPivot or the Excel 2013 Data Model you can leverage cube functions to obtain the layout you need. Part 2 explains how you can add sparkline charts and KPI status and trend icons. You can download the Excel workbook and data model used in the videos here in either Excel 2010 format or Excel 2013/2016 format.

Make your year-end reporting a snap with an Excelcraft Training Kit!

Excel Cube Formula Reporting with Microsoft Analysis Services

Excelcraft's multi-media training kits focus on building cube formula reports and dashboards with data sourced either from PowerPivot/Power BI data models or from Analysis Services Multidimensional cubes/Tabular models. Learn more...


© 2011-17 Excelcraft.com. Microsoft and Excel are registered trademarks of Microsoft Corporation in the USA and other countries.
Template design by Andreas Viklund

Terms of Use            Privacy            Cookies            Contact