GETPIVOTDATA Not Dragging Down Correctly

[ExcelAtEverything]

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.


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

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

 

[mart37]

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]

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]

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]

And when you delete the date part in the formula?

 

[ExcelAtEverything]

Delete how much of the date part(s)? I keep getting errors when I do that.

 

[mart37]

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

 

[mart37]

I think you need to add by the pivot table Distinct Count of OrderID the grand total by row.
See Sum of Quantity.

 

[ExcelAtEverything]

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]

=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!