How to use GETPIVOTDATA

[Pranesh]

Hi All,


I am trying to get a value from a pivot table using GETPIVOTDATA formula. Below is the formula.

GETPIVOTDATA("[Measures].[Distinct Count of id]",$A$3,"[Table1].[timeline]","[Table1].[timeline].&","[Table1].[approved (Month)]","[Table1].[approved (Month)].&[May]","[Table1].[approved (Year)]","[Table1].[approved (Year)].&[2018]")

In the above formula we have month as May and year as 2018. I want this to be replaced with cell range. In range A1 I have month updated manually and in A2 I have year updated. So in the above formula im trying to replace the month & year with range A1 & A2 but im getting #ref error. Below is the formula i modified. Can someone help me in fixing this pls.

GETPIVOTDATA("[Measures].[Distinct Count of id]",$A$3,"[Table1].[timeline]","[Table1].[timeline].&","[Table1].[approved (Month)]","[Table1].[approved (Month)].&[A1]","[Table1].[approved (Year)]","[Table1].[approved (Year)].&[A2]")

 

[mole999]

will the cell A3 not give you the idea ?

 

[Pranesh]

will the cell A3 not give you the idea ?

Hi mole999,

Sorry, i couldn't understand what you meant.

 

[Twollaston]

Hi mole999,

Sorry, i couldn't understand what you meant.

It looks like your pivot table is connected to power bi or something right?
I find it very tough to call field names from OLAP

When I'm trying to automate something here, I would just record your self a macro in which you take the cell you want to populate the information to, and you set it = to and grab the element from the pivot table, it should give you the correct code, or at least the correct field names

the $A$3 looks a little out of place as it's the only part that is not inside a bracket []

Is the information in your pivot table visible? What exactly are you trying to do? If I put something in cell A1 and try to reference it like its showing in a pivot table, then I get a REF error as well. Because you have to use pivot elements right? Can you show a picture of your pivot table, and a picture of what you want the cell to show as a result?

 

[Pranesh]

It looks like your pivot table is connected to power bi or something right?
I find it very tough to call field names from OLAP

When I'm trying to automate something here, I would just record your self a macro in which you take the cell you want to populate the information to, and you set it = to and grab the element from the pivot table, it should give you the correct code, or at least the correct field names

the $A$3 looks a little out of place as it's the only part that is not inside a bracket []

Is the information in your pivot table visible? What exactly are you trying to do? If I put something in cell A1 and try to reference it like its showing in a pivot table, then I get a REF error as well. Because you have to use pivot elements right? Can you show a picture of your pivot table, and a picture of what you want the cell to show as a result?

Hi Twollaston,

Thanks for your help here.

It is not using Power bi pivot, but its actually a data model pivot.

If i use a normal pivot i can easily change what im expecting but in data model pivot i find it very difficult.

I need distinct count of a value and that cannot be done through normal pivot and that is why im using data model pivot.

Range $A$3 refers to where the pivot is located. I have did a GETPIVOT on normal pivot(With Count) and data model pivot(With Unique count) as shown below.

Im not sure how to fix this.

Normal pivot code:

GETPIVOTDATA("unique_id",$A$3,"approved",May,"Years",2018)

Data model pivot code:

GETPIVOTDATA("[Measures].[Distinct Count of unique_id]",$A$3,"[Range].[approved (Month)]","[Range].[approved (Month)].&[May]","[Range].[approved (Year)]","[Range].[approved (Year)].&[2018]")

 

[Twollaston]

Hi Twollaston,

Thanks for your help here.

It is not using Power bi pivot, but its actually a data model pivot.

If i use a normal pivot i can easily change what im expecting but in data model pivot i find it very difficult.

I need distinct count of a value and that cannot be done through normal pivot and that is why im using data model pivot.

Range $A$3 refers to where the pivot is located. I have did a GETPIVOT on normal pivot(With Count) and data model pivot(With Unique count) as shown below.

Im not sure how to fix this.

Normal pivot code:

GETPIVOTDATA("unique_id",$A$3,"approved",May,"Years",2018)

Data model pivot code:

GETPIVOTDATA("[Measures].[Distinct Count of unique_id]",$A$3,"[Range].[approved (Month)]","[Range].[approved (Month)].&[May]","[Range].[approved (Year)]","[Range].[approved (Year)].&[2018]")

And when you put it with the cell references like this, does it work for you on a normal pivot?
GETPIVOTDATA("unique_id",$A$3,"approved",A1,"Years",A2)

Maybe it is because A1 and A2 are not being referenced the same way as $A$3, you have them in brackets and inside the quote. I'm not sure. Still not sure what you have in cell A1 or A2. What is the value inside these cells? are you trying to link your formula to a cell so you can change the year and month by changing the value in a cell?
GETPIVOTDATA("[Measures].[Distinct Count of id]",$A$3,"[Table1].[timeline]","[Table1].[timeline].&","[Table1].[approved (Month)]","[Table1].[approved (Month)].&[A1]","[Table1].[approved (Year)]","[Table1].[approved (Year)].&[A2]")

 

[Pranesh]

And when you put it with the cell references like this, does it work for you on a normal pivot?
GETPIVOTDATA("unique_id",$A$3,"approved",A1,"Years",A2)

Yes it works.

.[Distinct Count of id]",$A$3,"[Table1].[timeline]","[Table1].[timeline].&","[Table1].[approved (Month)]","[Table1].[approved (Month)].&[A1]","[Table1].[approved (Year)]","[Table1].[approved (Year)].&[A2]")

Yes, Im trying to link the formula to a cell so i can change the year and month by changing the value in a cell.


In A1 i have Jun and in A2 i have 2019


Adding A1 & A2 with in [] doesn't work.

 

[Twollaston]

Yes it works.



Yes, Im trying to link the formula to a cell so i can change the year and month by changing the value in a cell.


In A1 i have Jun and in A2 i have 2019


Adding A1 & A2 with in [] doesn't work.

I'm saying remove the bracket from the A2 part, and remove the quotes, and test it. Again, without seeing your data, I can't do any testing so you're going to have to play around with how it's entered try it without the quotes and brackets

"[Table1].[approved (Month)].&"A1
"[Table1].[approved (Year)].&"A2)

 

[Pranesh]

I'm saying remove the bracket from the A2 part, and remove the quotes, and test it. Again, without seeing your data, I can't do any testing so you're going to have to play around with how it's entered try it without the quotes and brackets

"[Table1].[approved (Month)].&"A1
"[Table1].[approved (Year)].&"A2)

Hi,

I found the solution in the below link. Thanks for your help and time.


https://www.excelforum.com/excel-charting-and-pivots/1121401-getpivotdata-problem-data-model.html

 

[Twollaston]

Hi,

I found the solution in the below link. Thanks for your help and time.


https://www.excelforum.com/excel-charting-and-pivots/1121401-getpivotdata-problem-data-model.html

Nice glad you found it bud
Have a good weekend!