How to use GetPivotData on data model Pivot Table

[SC713]

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

[#2 · Accepted solution]

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)

[#3]

@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

[#4]

@L z. 

That was very helpful. I was able to do what I needed to. Thank you

[#5]

@SC713 

Glad this helped & Thanks for posting back

If you don't mind please mark as solution to help those who search - Thanks

[#6]

Thanks @L z. . I had a similar question and thanks to you I have solved this issue. It was driving me crazy. Regards