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
 

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
I say that because I thought that when you have only the date column you then needed the date statement. And that without statement the grand total is used. When I removed the grand total then I get an error.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
300
Office Version
  1. 2019
Platform
  1. Windows
I say that because I thought that when you have only the date column you then needed the date statement. And that without statement the grand total is used. When I removed the grand total then I get an error.
Mart37, I see what you mean now. I keep getting errors as well. Initially my computer froze an hour ago and I lost all your fixes from earlier today. When I pulled it back up, I entered this latest fix and it worked great. But then I refreshed it to include all of my new data, and got all the errors. So anyway, I added the totals in and now it works great again. Thanks again bro! You have really come thru for me with this entire issue and I so greatly appreciate it! 🙏🙏🙏
 

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
300
Office Version
  1. 2019
Platform
  1. Windows
AAlso mart37, I've only known how to use Pivot Tables for a few days now, and I had no idea about that totals line and it causing errors if it's not there for whatever reason. I still don't quite understand why the sheet throws errors if the grand totals line isn't in. I've been looking it up online for about 15 minutes now but haven't been able to find anything about it yet. I'm going to keep searching for that so that I can fully understand it, but just wanted to reach back out and tell you thanks for that!
 

Watch MrExcel Video

Forum statistics

Threads
1,130,310
Messages
5,641,450
Members
417,210
Latest member
rins

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