Pivot table calculated field with MAX() doesn't work as expected

[toshimarise]

I am trying to count how many resources it takes people to complete various tasks. I am using a pivot table because the data table is huge, over 2000 rows for 600 people.

I need to know two pieces of information, for each person:
a) AVERAGE: the average resources of each type used for each type of task (I have this one figured out)
b) MAX: the largest number of any single type of resource used for any single task.

How do I make a calculated field that will calculate the max value correctly?

Here is an example data table:
[TABLE="width: 522"]
<colgroup><col><col span="6"></colgroup><tbody>[TR]
[TD]Employee
[/TD]
[TD]task ID[/TD]
[TD]task type[/TD]
[TD]Resource 1[/TD]
[TD]Resource 2[/TD]
[TD]Resource 3[/TD]
[TD]Resource 4[/TD]
[/TR]
[TR]
[TD]Doe, John[/TD]
[TD]6[/TD]
[TD]XYZ[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Doe, John[/TD]
[TD]3[/TD]
[TD]XYZ[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0
[/TD]
[/TR]
[TR]
[TD]Roe, Jane[/TD]
[TD]15[/TD]
[TD]ABC
[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TD]Roe, Jane[/TD]
[TD]29[/TD]
[TD]ABC[/TD]
[TD="align: right"]0
[/TD]
[TD="align: right"]0
[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Roe, Jane[/TD]
[TD]2[/TD]
[TD]XYZ[/TD]
[TD="align: right"]5
[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

And here is the pivot table I am currently using (zeroes appear as "-"):

[TABLE="width: 530"]
<colgroup><col><col><col span="4"><col></colgroup><tbody>[TR]
[TD]Row Labels
[/TD]
[TD] Count of task ID
[/TD]
[TD] Average of Resource 1 [/TD]
[TD] Average of Resource 2 [/TD]
[TD] Average of Resource 3 [/TD]
[TD] Average of Resource 4 [/TD]
[TD]Max of maxvalue
[/TD]
[/TR]
[TR]
[TD]Doe, John
[/TD]
[TD] 2
[/TD]
[TD] 3.5
[/TD]
[TD] 2.5
[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] 7.0
[/TD]
[/TR]
[TR]
[TD] XYZ
[/TD]
[TD] 2
[/TD]
[TD] 3.5[/TD]
[TD] 2.5[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] 7.0[/TD]
[/TR]
[TR]
[TD]Roe, Jane
[/TD]
[TD] 3
[/TD]
[TD] 1.7
[/TD]
[TD] 0.3
[/TD]
[TD] 1.7
[/TD]
[TD] 0.3
[/TD]
[TD] 5.0
[/TD]
[/TR]
[TR]
[TD] ABC
[/TD]
[TD] 2[/TD]
[TD] -
[/TD]
[TD] 0.5[/TD]
[TD] 2.5[/TD]
[TD] 0.5[/TD]
[TD] 5.0
[/TD]
[/TR]
[TR]
[TD] XYZ
[/TD]
[TD] 1[/TD]
[TD] 6.0
[/TD]
[TD] -
[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] 6.0
[/TD]
[/TR]
</tbody>[/TABLE]

The problem is the Max Value column. It is currently a calculated field with the formula:
= MAX('Resource 1','Resource 2','Resource 3','Resource 4' )

It acts the same results if the values are summarized by Sum instead of Max.

The desired results are:
[TABLE="width: 530"]
<tbody>[TR]
[TD]Row Labels
[/TD]
[TD] Etc
[/TD]
[TD] Max of maxvalues:
[/TD]
[/TR]
[TR]
[TD]Doe, John
[/TD]
[TD] ...
[/TD]
[TD] 5
[/TD]
[/TR]
[TR]
[TD] XYZ
[/TD]
[TD] ...
[/TD]
[TD] 5
[/TD]
[/TR]
[TR]
[TD]Roe, Jane
[/TD]
[TD] ...
[/TD]
[TD] 6
[/TD]
[/TR]
[TR]
[TD] ABC
[/TD]
[TD] ...
[/TD]
[TD] 3
[/TD]
[/TR]
[TR]
[TD] XYZ
[/TD]
[TD] ...
[/TD]
[TD] 6
[/TD]
[/TR]
</tbody>[/TABLE]

Thank you in advance for any help you can give!

 

[liveinhope]

I think that calculated fields work only on "sum of " whatever field . Go and create a new calculated field - the fields are displayed and you click on the fields that you want to be in your formula
(in your case Resiurce1,Resource2,etc ) but notice e how you don't get to specify if that means "sum of resource1" or "average of resource 1" or "max of resource1" ,etc

what you can do is put a new field in your source data - if resource1 to resource4 are in columns D-G then put this in H =MAX(D2:G2)

now redo your pivot table - should look like mine once you adjust the default value field settings


[TABLE="width: 763"]
<TBODY>[TR]
[TD][/TD]
[TD]Values[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row Labels[/TD]
[TD]Average of Resource 1[/TD]
[TD]Average of Resource 2[/TD]
[TD]Average of Resource 3[/TD]
[TD]Average of Resource 4[/TD]
[TD]Max of rmax[/TD]
[/TR]
[TR]
[TD]Doe, John[/TD]
[TD="align: right"]3.50[/TD]
[TD="align: right"]2.50[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]XYZ</SPAN>[/TD]
[TD="align: right"]3.50</SPAN>[/TD]
[TD="align: right"]2.50</SPAN>[/TD]
[TD="align: right"]0.00</SPAN>[/TD]
[TD="align: right"]0.00</SPAN>[/TD]
[TD="align: right"]5</SPAN>[/TD]
[/TR]
[TR]
[TD]Roe, Jane[/TD]
[TD="align: right"]1.67[/TD]
[TD="align: right"]0.33[/TD]
[TD="align: right"]1.67[/TD]
[TD="align: right"]0.33[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]ABC</SPAN>[/TD]
[TD="align: right"]0.00</SPAN>[/TD]
[TD="align: right"]0.50</SPAN>[/TD]
[TD="align: right"]2.50</SPAN>[/TD]
[TD="align: right"]0.50</SPAN>[/TD]
[TD="align: right"]3</SPAN>[/TD]
[/TR]
[TR]
[TD]XYZ</SPAN>[/TD]
[TD="align: right"]5.00</SPAN>[/TD]
[TD="align: right"]0.00</SPAN>[/TD]
[TD="align: right"]0.00</SPAN>[/TD]
[TD="align: right"]0.00</SPAN>[/TD]
[TD="align: right"]5</SPAN>[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD="align: right"]2.4[/TD]
[TD="align: right"]1.2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.2[/TD]
[TD="align: right"]5[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL span=4><COL></COLGROUP>[/TABLE]

 

[toshimarise]

Adding an extra column to the source table worked beautifully. Can't believe I didn't think of it myself.

Thanks!

 

[liveinhope]

youre welcome