return maximum value from a pivot table

[StillUnderstanding]

Hello,

I wonder if anyone can help me please? I am using the following code in a cell to return the maximum value in a pivot table =INDEX(A6:A15,MATCH(MAX(B6:B15),B6:B15,0))

The issue that I have is that when the pivot table changes size its only returning the value within the code.

I think you can do this with getpivotdata but I am not sure how?

Can anyone help please?

The table is called PivotTable1

 

[GraH]

You might try

index(a:a, match(large(b:b, 2), b:b,0))

 

[jorismoerings]

Hi,

If you want to this with GETPIVOTDATA you need to add a field to the pivot however without knowing how your data table is setup that's impossible without assuming to much.
And just as a note: your formula as well as the proposal from @GraH does not come back with the max value but with the month containing the max value.

 

[GraH]

Or use a DAX measure so the pivot only returns this value. That is when loading the data in the datamodel would be an option.

 

[StillUnderstanding]

Thanks @GraH and @jorismoerings for the help on this.

@GraH you formula works well to bring back the highest month!

I am also trying to bring back the highest number so I have managed to use this formula =GETPIVOTDATA("Session",'Onboarding Pivots'!$B$5) the problem is that it brings back the total, so 470 and not 108 as I would have expected.

Do you know how I would change the formula to bring back the largest number and exclude the grand total?

Thanks again for your help so far.

 

[GraH]

Getpivotdata only returns a value from given coordinates. In this case it returns the grand total because you do not specify the month.
I don't know how to twist it to return the max value.
You may consider not to show grand total on the pivot and use max on the (dynamic) range.
Also you already have a working formula that returns that value.
Edit:
Another twist is to sort the pivot values in descending order. The max value is then always the first.

 

[jorismoerings]

Hi,

GETPIVOTDATA only returns data that's available in the pivot. So without having a MAX value in the pivottable or by creating the Calculating field that does the MAX for you, getpivotdata will not work.