How to GetPivotData from Data Model Pivot Table

tb426

New Member
Joined
May 8, 2013
Messages
13
Hello all,

need some help on this... Have been trying to adjust my usual formula but doesn't work...

I know I need to change my GETPIVOTDATA formula a bit when the Pivot Table is using Data Model, to [Measures].][]

I have a Pivot Table in a sheet named Not Ready Table2, it start at Cell B2. Rows has regular labels Reason Code (such as Lunch[9], Scheduled Breaks[1], Coaching[5], etc.), Columns has regular label Date (11/01/2019, 11/02/2019, etc.), and Values has a Measure named Duration (which is time HH:MM:SS).

I am trying to get data from within the pivot table but doesn't seems to work. My current formula is:

=IFERROR(GETPIVOTDATA("[Measures].[Duration ]",'Not Ready Table2'!B2,"Reason Code","Scheduled Break[1]","Date",DATE(2019,11,1)),"")

and when I press enter it returns blank.

I attached a small sceenshot of what it looks like
 

Attachments

  • Capture.JPG
    Capture.JPG
    19.2 KB · Views: 45

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,414
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Those field names look a bit short to me. If you let Excel create the GETPIVOTDATA formula for that cell, what does it look like?
 

tb426

New Member
Joined
May 8, 2013
Messages
13
Hi Rory...

you mean like this...

=GETPIVOTDATA('Not Ready Table2'!B2,'Not Ready Table2'!B2,'Not Ready Table2'!B3,'Not Ready Table2'!B18,'Not Ready Table2'!C2,'Not Ready Table2'!C3)

but it gives back #REF...


The Duration is a measure within my Data Model. I believe it needs to be something like [Measures].[Duration]

I guess my question would be how to I get the pivotdata for say the Schedule Break time 00:29:39 on 11/1/2019? The Pivot Table starts in Cell B2
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,414
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
No, I mean that, assuming you have the option for Excel to generate GETPIVOTDATA formulas automatically turned on, just type = then select the value cell your formula should be returning. That will show you want the formula should look like, and we can then add in the cell references to replace hardcoded values.
 

tb426

New Member
Joined
May 8, 2013
Messages
13
AH GOT IT... :)

this is what it looks like...

=GETPIVOTDATA("[Measures].[Duration ]",'Not Ready Table2'!$B$2,"[NotReadyNov].[Reason Code]","[NotReadyNov].[Reason Code].&[Scheduled Break[1]]]","[NotReadyNov].[Date]","[NotReadyNov].[Date].&[2019-11-01T00:00:00]")

but it is kind of messy though... is it possible to clean it up to maybe make the formula looks nicer and cleaner, cause I will be have multiple field (Coaching, Lunch, etc.) for each and every day (2019,11,01, 2019, 11, 02, etc.)
 

Watch MrExcel Video

Forum statistics

Threads
1,126,965
Messages
5,621,871
Members
415,862
Latest member
nascaline

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