Results 1 to 4 of 4

Thread: GetPivotData (Pivot Table vs Power Pivot Table) behavior

  1. #1

    Question GetPivotData (Pivot Table vs Power Pivot Table) behavior

    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Attached Files Attached Files

  2. #2
    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.

  3. #3
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Nanaimo, BC, Canada
    Blog Entries

    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.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: -||- Blog: -||- Forums:
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  4. #4
    Ken -
    Thanks for the information. I'll have to look into cube functions. Is there any performance hit?

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts