When creating a Pivot Table one can easily apply the Maximum function for figures in the Value area section by right clicking and choosing Maximum from “Summarise Values By” > Maximum. The maximum so computed for figures in the Value area section is a “conditional maximum” because the numbers in the Value area section will be affected by whatever fields are dropped into/dragged out of the Row label, column label and Report Filter sections.
However, think of a case where you have already dragged 2 Fields in the Report Filter section, one in the Row label section and one numeric field in the Value area section (the summarization function used for figures in the Value area section is “Maximum”). The requirement now is to drag another field in the Row label section without affecting the figures (which are summarized by the Maximum function under Summarise Value By) appearing in the Value area section.
You may refer to the actual dataset, expected solution and my solution in this workbook.
I have used Power Query (a free add-in by Microsoft for MS Excel 2010 and higher versions) to resolve this problem.
Drop additional fields in the Row area of a Pivot Table without affecting the already computed “conditional maximum” in the Value Area section
{ 2 Comments }