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.
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.
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.
How to handle imported cumulative monthly accounting balances
This multi-part section describes how you can add assumptions and modeling capabilities to you Power Pivot Data Model.
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.
This video will help you learn:
- if your version of Excel 2013 has it
- some advantages the Data Model offers
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.
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
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.
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...