Excel Power Pivot Formula Manipulation

Question

When I worked with regular pivot tables, i really enjoyed the ability to manipulate the formula based on cell values to automate what information I'd like to see dynamically.

Regular Pivot Table Example:

Standard Equation: =GETPIVOTDATA("NUM_ROOMS",$C$3,"STAY_DATE",DATE(2015,9,16))

Modified Equation (to automate what info it finds based on cell values): =GETPIVOTDATA(TEXT($A8,""),$C$3,TEXT($A9,""),C$1)

I'm really liking the power of a Power Pivot with multiple sources, but I cannot figure out how to manipulate the Power Pivot formulas in the same manner as I did the regular.

I connected a few Excel flat files containing tables (see below)

The Power Pivot Formula is a bit different. It has a different format with respect to your connected tables. I cannot figure out how to manipulate these formulas in the same way without getting error values. See screen shot below:

Can someone help!?

Answer 1

The =GETPIVOTDATA formula will only show cells that are already visible in the PivotTable currently. So if you have your PivotTable filtered to 2016 and your GETPIVOTDATA formula asks for 2015 you get nothing.

An alternate approach is to use =CUBEVALUE Excel functions as they query the Power Pivot data model directly independent of what's in a PivotTable. An easy way to get started is to go to PivotTable Tools... Analyze ribbon then from the OLAP Tools pulldown choose Convert to Formulas. That will convert the selected PivotTable to CUBEVALUE formulas.

See Using Excel CUBE Functions with Power Pivot for more details.

You have already discovered that both GETPIVOTDATA and CUBEVALUE use MDX unique member names like [Measures].[Sum of Rooms]. The nice thing is that you get intellisense when you start typing CUBEVALUE formulas.

These Excel formulas help you build highly custom formatted reports not possible with the rigid PivotTable formatting limits. Of course if you don't need to customize the format of a report on the Excel sheet more than a PivotTable allows but are needing to code complex analytical calculations that display in the body of a PivotTable I would look at DAX calculated measures that you code inside the Power Pivot window.