# PIVOT Table, MAX formula

Copper Contributor

# PIVOT Table, MAX formula

Hello Team,

I am seeking some help and a solution to my issue. If it was already covered, kindly point it out to me. Thank you.

Example of the report on the link: File

It is an inventory file that helps me when discussing my partner's orders/sellouts and inventory.
I see this report on a single Excel page, with the first column listing the products and subsequent columns providing details on inventory, sales, and orders. Our clients report their inventory and sales weekly, and we retrieve their orders from our ERP system. This layout makes it easy (for me) to analyze the data quickly and efficiently, facilitating better decision-making in our order planning.

Here's how I see the report:

 Product Last Week inventory Total Sales Total Orders Product A [Quantity] [Quantity] [Quantity] [Quantity] Product B [Quantity] [Quantity] [Quantity] [Quantity]

My problem:
Inventory - the aggregation functions of the Pivot Tables logic is clear; it summarizes the total amount at the end of the row/line. But what I would like to see is the inventory from the last week, hence the reason I am using the MAX value for the string. But at the total line it shows the MAX number from the row, and not the latest value from the MAX date, which I need. I googled the "GETPIVOTDATA" formulas, but it is either not built for this, or I have a lack of knowledge (2nd option for sure). I can do a table with PIVOTBY, and SORTBY array formulas, but each time I refresh the DATA SHEET report (it is connected through Power Query to our system), it breaks the references, and I get #REF, #VALUE, and #CALC errors. So if you like challenges :), kindly have a look at the below. Thank you very much again for your time.

4 Replies

# Re: PIVOT Table, MAX formula

Hello @Vitalie_C

With Power Pivot (assuming you run Excel / Windows):

A few intermediate Measures are not visible in the Pivot fields but are in the Model

# Re: PIVOT Table, MAX formula

Hi @Lorenzo, i see that i works as requested, thank you ! Can you help me with the steps too?

Thank you

# Re: PIVOT Table, MAX formula

Can you help me with the steps too?

What do you exactly mean/wants (currently away - don't expect timely reply)

# Re: PIVOT Table, MAX formula

At the bottom of each reply you get on this website there's a Mark as response link. Clicking it helps people who Search - Thanks