Sum of Max instead of Max of Max in pivot table with grouping

[Shahista]

Hi,

I have excel with Column A = Text, Column B = Text, Column C = Text, Column D = Number.

I created pivot table Column A, B, C as Rows and Column D as Values and grouped rows.

Now when i summarize the pivot table with sum, its giving me sum of all the numbers present in Column D, instead I need to show only sum of max number in each group.


Please do help. I am very new to pivot table

[xlnitwit]

Hi,

You can have Sum or Max but not both in a regular pivot table. If you have Power Pivot available to you, it is possible to have the max at the detail level and sum those at the group level.

[Shahista]

Hello,

Thank you so much for responding so quickly.

Can you please explain how to get sum at group level and max in detail level using power pivot?


Thanks in Advance.

[xlnitwit]

Can you provide an example workbook please- preferably with an example of the results you want from the data set just to be certain we are aiming at the same thing? The steps will most likely be easier to follow in the context of your file.

[Shahista]

Please find below data for the sample.

I tried to attach file but could not get option so copying the data.


ColumnA ColumnB ColumnC ColumnD ColumnE
PM004 ASAB 28-Jun 41 Jun
PM004 ASAB 29-Jun 51 Jun
PM004 ASAB 30-Jun 41 Jun
PM032 ASAB 14-Jun 10 Jun
PM032 ASAB 28-Jun 20 Jun
PM032 ASAB 29-Jun 10 Jun
PM032 ASAB 30-Jun 10 Jun


Pivot Table :
Row Labels Max of ColumnD
Jun 51
PM004 51
ASAB 51
28-Jun 41
29-Jun 51
30-Jun 41
PM032 20
ASAB 20
14-Jun 10
28-Jun 20
29-Jun 10
30-Jun 10

I want sum of max in Jun group. That is when i group Jun i should see 71 not 51.



Thanks

[xlnitwit]

Here are the steps to achieve that for your layout

1. Convert your data to a table (I will use Table1 as the name here)
2. Load the table into the data model
3. Create a pivot table and add the row fields you want
4. Create a new measure named 'MaxValue' using the formula: =MAX(Table1[ColumnD])
5. Create another new measure named MonthlyMax using the formula: =IF(COUNTROWS(VALUES(Table1[ColumnE]))=1,sumx(values(Table1[ColumnA]),[MaxValue]),[MaxValue])
6. Add the MonthlyMax measure to the pivot table.

[Shahista]

Looks great, Thank you for the solution

But still its giving bit trouble.

Formula : =IF(COUNTROWS(VALUES(Table1[ColumnE]))=1,sumx(values(Table1[ColumnA]),[MaxValue]),[MaxValue])

giving the value as "#NAME?" in the Table1.

Also =MAX([ColumnD]) gives only 51 in entire column MaxValue which should be 51 and 20 based on ColumnA as well.

Can you please suggest on the same.

Thank You

[xlnitwit]

Those are not formulas to go into the source table. They are measures that you add to the pivot table.

[Shahista]

Thanks a ton. It worked great

[xlnitwit]

You're welcome.