GetPivotData help

[AthmanArumugam]

Hi,

How to get the values from Pivot table using getpivotdata formula if the sum of value appears in a column. Find attached the excel sheet where in I am trying to the Stream based on the Hours value. When I use the formula =GETPIVOTDATA("Stream",A4,"Actual CP","2403"). Can you kindly help me here.

 

[Deepak]

Have u checked this...

http://chandoo.org/wp/2015/08/26/getpivotdata-in-dashboards/

 

[p45cal]

I can't find any ref to Hours in the workbook you attached, so this is a complete guess:
=GETPIVOTDATA("Actual CP",$A$3,"Stream","App Management")

 

[jeffreyweir]

Further to what p45cal says, Excel will actually construct the GETPIVOTDATA formula for you, if you have the Generate GETPIVOTDATA enabled:

Simply type an = in the cell where you want your formula, then select the cell in the PivotTable that you want to reference. Excel will then create the formula you need, as in the screenshot below:

 

[NARAYANK991]

Hi ,

A Pivot Table is a way of summarizing data ; the field called Stream is the Row Labels field , and since it is a pivot table , you are guaranteed that these labels will be unique.

There is no guarantee that the values field , which is the Sum of Actual CP , will have unique values ; it is always possible that there will be two identical values.

Even otherwise , I do not see the objective of retrieving the Stream corresponding to the Sum of Actual CP values. If at all you wish to find the Top 5 / Bottom 5 streams , you can always sort the pivot table based on the Sum of Actual CP values , and then take the first 5 row labels.

Just BTW , never include the Total row of your data range in the pivot table data source range , since the pivot table anyway has a Grand Total facility available , and including the Total row of your data range puts off this Grand Total value.

Narayan