GETPIVOTDATA Not Dragging Down Correctly

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
300
Office Version
  1. 2019
Platform
  1. Windows
Hello,
I have a document which will be a daily report for a store which will report on sales and other metrics. The main sheet gets its information from Pivot Tables (each located on a separate tab). The Pivot Tables get their information from Excel documents which are stuffed into specific folders on my desktop, and I get that information into the Pivot tables via Power Query where it's then just loaded into the data model (which again, feeds the Pivot Tables).

The GETPIVOTDATA formula below is for the "Main" sheet "IPT" row. (IPT is calculated by taking # of items sold divided by total number of transactions. The formula works, but when I pull it down, it keeps returning the same number instead of auto-adjusting the formula so that it goes for the next row down, etc. How can I adjust the GETPIVOTDATA formula below so that when I drag it downward, it works correctly? I tried changing both instances of "[Monthly Orders Reports].[Org #].&[237]" into this: "[Monthly Orders Reports].[Org #].&["&[@[Org '#]]&"]"), but it doesn't work.

Note: The Monthly Orders Reports is the folder in my desktop which dolds the info for the Pivot Tables used to get the IPT number.
=GETPIVOTDATA("[Measures].[Sum of Quantity]",'Item Totals Yest'!$B$3,"[Monthly Orders Reports].[Org #]","[Monthly Orders Reports].[Org #].&[237]","[Monthly Orders Reports].[Date]","[Monthly Orders Reports].[Date].&[2021-03-27T00:00:00]")/GETPIVOTDATA("[Measures].[Distinct Count of Order ID]",'Trans Count Yest'!$B$3,"[Monthly Orders Reports].[Org #]","[Monthly Orders Reports].[Org #].&[237]","[Monthly Orders Reports].[Date]","[Monthly Orders Reports].[Date].&[2021-03-27T00:00:00]")

Sheet: Main

Table: Yesterday
Description: This is the main home page of the report which contains the all the results from all of the queries, all on one sheet.
1616995126133.png

Sheet: Trans Count Yest
Pivot Table Name: Trans_Cnts_Yest
Description: Total Transactions Yesterday
1616995581993.png


Sheet: Item Totals Yest
Pivot Table Name: Item_Tot_Yest
Description: Total Items Sold Yesterday

1616996234141.png
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
Lets split the formula.
When you first calculate this:
=GETPIVOTDATA("[Measures].[Sum of Quantity]",'Item Totals Yest'!$B$3,"[Monthly Orders Reports].[Org #]","[Monthly Orders Reports].[Org #].&[" & [@[Org '#]] & "]","[Monthly Orders Reports].[Date]","[Monthly Orders Reports].[Date].&[2021-03-27T00:00:00]")
or this:
=GETPIVOTDATA("[Measures].[Sum of Quantity]",'Item Totals Yest'!$B$3,"[Monthly Orders Reports].[Org #]","[Monthly Orders Reports].[Org #].&[" & E6 & "]","[Monthly Orders Reports].[Date]","[Monthly Orders Reports].[Date].&[2021-03-27T00:00:00]")
 

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
300
Office Version
  1. 2019
Platform
  1. Windows
Mart37 thank you again! That works flawlessly! There is however a problem that has come to light since then, which makes it not work any longer.

Once this sheet is done, these will be pulling info from my Pivot Tables for 'yesterday'. Each morning, the user will upload the latest sheets which show all of the sales data from the prior day. I have only been updating the info every few days as I build the sheet though. The last time I updated it, it was 3/27/2021. If you look at the GETPIVOTDATA formula, the dates always call for the date: 3/27/2021. When I first applied your fix here, it worked. But that was when my Pivot Table 'yesterday' date still showed 3/27/2021. Moments ago however, I updated everything thru 3/29 (yesterday)... and it broke the sheet throwing errors everywhere.

How do I change that portion of the formula so that it always grabs the info that resides at that spot, regardless of the date. I tried switching "[Monthly Orders Reports].[Date].&[2021-03-27T00:00:00]" to $C4, but that didn't seem to work.

Thanks.
 

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
300
Office Version
  1. 2019
Platform
  1. Windows
Mart37 I marked that as the answer because it was definitely the answer as I relayed it to you. While that worked perfectly if the date was going to remain as 3/27/2021 forever (LOL), It seems like this will just work better because of the date thing. ='Item Totals Yest'!$D6/'Trans Count Yest'!$C6
 

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

And when you delete the date part in the formula?
 

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
300
Office Version
  1. 2019
Platform
  1. Windows
Delete how much of the date part(s)? I keep getting errors when I do that.
 

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

=GETPIVOTDATA("[Measures].[Sum of Quantity]",'Item Totals Yest'!$B$3,"[Monthly Orders Reports].[Org #]","[Monthly Orders Reports].[Org #].&[" & [@[Org '#]] & "]")
 
Solution

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
I think you need to add by the pivot table Distinct Count of OrderID the grand total by row.
See Sum of Quantity.
 

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
300
Office Version
  1. 2019
Platform
  1. Windows
I think you need to add by the pivot table Distinct Count of OrderID the grand total by row.
See Sum of Quantity.
Why do you say I need to add that? The grand total will always be the same because the Pivot Table will only ever show that one "yesterday" column. In fact, that grand total column is in the other table by mistake. I just hadn't deleted it out yet. Is there some reason you're saying I need it there, or were you only saying that because that's the way the other table was?
 

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
300
Office Version
  1. 2019
Platform
  1. Windows
=GETPIVOTDATA("[Measures].[Sum of Quantity]",'Item Totals Yest'!$B$3,"[Monthly Orders Reports].[Org #]","[Monthly Orders Reports].[Org #].&[" & [@[Org '#]] & "]")
BOOM! That's the one! Thx so much, mart37!
 

Watch MrExcel Video

Forum statistics

Threads
1,130,309
Messages
5,641,448
Members
417,209
Latest member
Agbarker

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