Getpivotdata issue

[ruthhacche]

I have a table created in power query. I can pivot out of it and it works fine and referencing into the pivot using GETPIVOTDATA works fine for any cell in the pivot.

If I add the data into my data model and pivot out of that then it does not work.

Using GETPIVOTDATA on a total row works:

=GETPIVOTDATA("[Measures].[Sum of Gross_Sale__c]",$A$1) = value

But if I try and get a single point in the pivot it does not work - If PimraID is totally collapsed so the whole of USA WH is one line then it does not like it:

=GETPIVOTDATA("[Measures].[Sum of Gross_Sale__c]",$A$1,"[PickPack ORDERS].[PimraID]","[PickPack ORDERS].[PimraID].&[USA WH]") = #REF!

But if I expand the Pimra ID so that USA WH is a total of several items then it does work again.

=GETPIVOTDATA("[Measures].[Sum of Gross_Sale__c]",$A$1,"[PickPack ORDERS].[PimraID]","[PickPack ORDERS].[PimraID].&[USA WH]") = value

So I have to have my pivot fully expanded before I can pick up figures from it using GETPIVOTDATA - am I going mad? Can anyone explain why this is happening?

 

[GraH]

This is the way it works. You are not going mad.

The getpivotdata does refer to the coordinated fields (Rows/Columns) and they must be visible. Collapsed, the detailed fields are not "calculated".

Maybe you can use a Pivot without details and a second one, all expanded, in which you use the GETPIVOTDATA?

Or since you are using PowerPivot, create the pivot and then in the context ribbon go to OLAP Tools and choose "Convert to Formulas".

See if they make sense to you...