How to use GetPivotData on data model Pivot Table

Hello,

I am having a hard time using GetPivotData on my pivot table. I've followed online guides. The difference between my pivot table may be that the original tables are added to data model (so I can use a function to calculate median). My pivot table rows each correspond to a unique serial number, with a number of properties per serial number. I want to be able to enter a serial number, and populate the associated properties.

=GETPIVOTDATA("[Measures].[Median UT]",$A$3,"[BatchTensileData].[Work Order-Batch #]","[BatchTensileData].[Work Order-Batch #].&[123456789-01]")

I'd imagine I could just highlight the SN cell (a cell where I manually input a SN I wanna pull data for - M4) for the "item 1" entry, but cannot seem to make it work.

But changing "item 1" out with my cell gives me a Ref error.

=GETPIVOTDATA("[Measures].[Median UT]",$A$3,"[BatchTensileData].[Work Order-Batch #]","[BatchTensileData].M4")

=GETPIVOTDATA("[Measures].[Median UT]",$A$3,"[BatchTensileData].[Work Order-Batch #]",M4)

Even changing out only the SN part and keeping the earlier "BatchTensileData" reference results in a ref error.

=GETPIVOTDATA("[Measures].[Median UT]",$A$3,"[BatchTensileData].[Work Order-Batch #]","[BatchTensileData].[Work Order-Batch #].&[M4]")

Any ideas?

Thanks for your help

excel

Formulas and Functions

Lorenzo
Jun 30, 2023
Hi SC713

That should be:
=GETPIVOTDATA( "[Measures].[Median UT]",$A$3, "[BatchTensileData].[Work Order-Batch #]", "[BatchTensileData].[Work Order-Batch #].&[" & M4 & "]" )
Carefully look at how the concatenation is constructed to "encapsulate" the value of cell M4 between the last brackets
(If you would use CUBEVALUE this would be the same syntax)

Lorenzo
Silver Contributor
Jun 30, 2023
SC713
As a side note in case you're not aware. GETPIVOTDATA retrieves values that display on a PivotTable, couple of examples below:

In F3 & G3 we want the Total Value for Product A. It doesn't display on the PivotTable ==> #REF with GETPIVOTDATA. On the other hand we get it with CUBEVALUE

In F6 & G6 we want the Total Value for Product A and Date = Jun 1, 2023, no problem. However if we collapse Jun in the PivotTable:

Corresponding sample attached
SampleGETPIVOTvsCUBEVALUE.xlsx136 KB
- SC713
  Copper Contributor
  Jun 30, 2023
  Lorenzo
  
  That was very helpful. I was able to do what I needed to. Thank you
  - Lorenzo
    Silver Contributor
    Jul 01, 2023
    SC713
    Glad this helped & Thanks for posting back
    If you don't mind please mark as solution to help those who search - Thanks
Lorenzo
Silver Contributor
Jun 30, 2023
Hi SC713

That should be:
=GETPIVOTDATA( "[Measures].[Median UT]",$A$3, "[BatchTensileData].[Work Order-Batch #]", "[BatchTensileData].[Work Order-Batch #].&[" & M4 & "]" )
Carefully look at how the concatenation is constructed to "encapsulate" the value of cell M4 between the last brackets
(If you would use CUBEVALUE this would be the same syntax)
- labferjane
  Copper Contributor
  Feb 26, 2024
  Thanks Lorenzo . I had a similar question and thanks to you I have solved this issue. It was driving me crazy. Regards

Forum Discussion

How to use GetPivotData on data model Pivot Table

Share

Resources