GetPivotData (Pivot Table vs Power Pivot Table) behavior

Charles Banks

New member
Joined
Jul 11, 2012
Messages
5
Reaction score
0
Points
0
Background information:
  • Dashboard is in Excel 2010 using linked data connections to SQL server tables/views
  • This is working perfectly and is easy to update using the getpivotdata function from pivot tables. I update 1 field and all the supplemental graphs and tables are updated correctly
  • I'm contemplating migrating the dashboard to utilizing Power Pivot Tables as a revision because of slicers, sheer number of records and data set portability as well as additional data requests from my users

What I have noticed is that the getpivotdata syntax is different for Power Pivot and "normal" pivot tables. I have created a sample document with the two behaviors.

on pivot tables the syntax is =+GETPIVOTDATA("Sales",$A$15,"Store",1,"Period",4) and I am able to replace any of the measures with cell references. For example: =+GETPIVOTDATA("Sales",$A$15,"Store",1,"Period",$A$25)

on pivot tables created using power pivot data the syntax is =+GETPIVOTDATA("[Measures].[Sum of Sales]",$A$29,"[Table1].[Store]","[Table1].[Store].&[1]","[Table1].[Period]","[Table1].[Period].&[4]")

What I am trying to figure out is how the formula needs to be written to allow the functionality I experienced with the "normal" pivot tables

Thank you in advance for you time.

Charles Banks
 

Attachments

  • GetPivotData.xlsx
    103.7 KB · Views: 1,099

Charles Banks

New member
Joined
Jul 11, 2012
Messages
5
Reaction score
0
Points
0
Thanks to all that looked into this. I have found the solution. For those of you curious to create a floating reference for powerpivot pivot tables the syntax is as follows:

=+GETPIVOTDATA("[Measures].[Sum of Sales]",$A$29,"[Table1].[Store]","[Table1].[Store].&[1]","[Table1].[Period]","[Table1].[Period].&["&A45&"]")

This applies to the above example.
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
5
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Charles,

Sorry I didn't get here in time to help you through this. In addition, you can also use Excel's Cube formulas against PowerPivot data as well. The advantage of this is that you don't need the PivotTable sitting in a worksheet if all you want is one value from it.
 

Charles Banks

New member
Joined
Jul 11, 2012
Messages
5
Reaction score
0
Points
0
Ken -
Thanks for the information. I'll have to look into cube functions. Is there any performance hit?
 
Top