How to use GETPIVOTDATA

Pranesh

Board Regular
Joined
Jun 29, 2014
Messages
219
Hi All,


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

Code:
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.

Code:
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]")
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
will the cell A3 not give you the idea ?
 

Twollaston

Board Regular
Joined
May 24, 2019
Messages
235
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?
 
Last edited:

Pranesh

Board Regular
Joined
Jun 29, 2014
Messages
219

ADVERTISEMENT

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:
Code:
GETPIVOTDATA("unique_id",$A$3,"approved",May,"Years",2018)

Data model pivot code:
Code:
GETPIVOTDATA("[Measures].[Distinct Count of unique_id]",$A$3,"[Range].[approved (Month)]","[Range].[approved (Month)].&[May]","[Range].[approved (Year)]","[Range].[approved (Year)].&[2018]")
 
Last edited:

Twollaston

Board Regular
Joined
May 24, 2019
Messages
235
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:
Code:
GETPIVOTDATA("unique_id",$A$3,"approved",May,"Years",2018)

Data model pivot code:
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

Board Regular
Joined
Jun 29, 2014
Messages
219

ADVERTISEMENT

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.

Maybe it is because A1 and A2 are not being referenced the same way as $A$3 said:
.[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

Board Regular
Joined
May 24, 2019
Messages
235
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

Board Regular
Joined
Jun 29, 2014
Messages
219
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,269
Messages
5,641,219
Members
417,201
Latest member
SadiaAslam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top