Planning & Analysis

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. Watch the video.

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. Watch the video.

This video
shows 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.

P&L account balances imported from an accounting system/ERP general or nominal ledger are often cumulative, year-to-date balances. This article
shows one way to extract monthly values in your Data Model.

How you can add assumptions and modeling capabilities to you Power Pivot Data Model. Read 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.

How to use Excel and Power Pivot to leverage your use of transaction categorization by Class in QuickBooks data. Read more >

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.

In an Excel Cube Formula report a CUBEVALUE function may sometimes legitimately return no data for that intersection in the database (that product was not sold in that region during that time period). A calculation in the worksheet that refers to the blank CUBEVALUE cell involving +, -, *, / and some other calculations will likely return a #VALUE error. This occurs because the apparently blank CUBEVALUE cell is actually returning a zero-length text value, which causes many numerical calculations to fail. Wrapping your CUBEVALUE functions with IFERROR() that returns zero on error is one way to fix the problem. But there is a somewhat more elegant approach for most situations, that uses a little-known Excel function, N().

The function tool tip describes N as:

Converts non-number value to a number, dates to serial numbers, TRUE to 1, anything else to 0 (zero).

Depending on how many downstream calculations you have pointing to your CUBEVALUE functions you can decide if it is easier to wrap the CUBEVALUE functions in N functions or (if there are a small number of downstream calculations) wrap the components of the cell calculations that reference a CUBEVALUE function. Watch this 3 minute video for more explanation.

The function tool tip describes N as:

Converts non-number value to a number, dates to serial numbers, TRUE to 1, anything else to 0 (zero).

Depending on how many downstream calculations you have pointing to your CUBEVALUE functions you can decide if it is easier to wrap the CUBEVALUE functions in N functions or (if there are a small number of downstream calculations) wrap the components of the cell calculations that reference a CUBEVALUE function. Watch this 3 minute video for more explanation.

Share by: