How to correctly sum summarised Fields in a PivotTable with a calculated field

Question

I have a table with dates, and two value columns, Item 1 and Item 2.

I want to make a PivotTable with the maximum monthly value for Item 1 and Item 2.

This works as expected. I then want to sum those maximum values, to get a total of the maximum for Item 1 and Item 2, in the PivotTable.

I have tried used a calculated field with both:

=SUM(MAX(' Item 1'), MAX(' Item 2')) and =SUM('Item 1','Item 2').

But this sums all the values from that month for Item 1 and Item 2, not only the maximums. It is easy to do with a formula outside the PivotTable, but I would like to do this within the PivotTable so I can chart it and add new data in the future. The image below shows the results of the two calculated fields described above (in red), and a simple formula outside the PivotTable, which gives the desired result (in green).

I am using Excel Professional Plus 2016 on Windows 10. Below is example data if you want to try.

Date    Item 1  Item 2
01/01/2020  2   8
02/01/2020  3   6
03/01/2020  5   4
04/01/2020  3   7
05/01/2020  5   4
06/01/2020  2   7
07/01/2020  3   4
01/02/2020  7   6
02/02/2020  3   4
03/02/2020  6   3
04/02/2020  3   7
05/02/2020  2   8
06/02/2020  5   7
07/02/2020  5   3
01/03/2020  3   8
02/03/2020  5   6
03/03/2020  7   3
04/03/2020  8   7
05/03/2020  3   4
06/03/2020  2   2
07/03/2020  5   5

Answer 1

I suspect the plain pivot table function in excel is not enough to compute the min() and max() in a certain pivot table set, since the min() and max() functions are supposed to work in a row-by-row basis only. You need to create a "measure" (e.g. max of a certain group of cells, you may say they are calculated fields which can be manipulated more) to tell excel the expression to compute the value for a group of cells for you. This does not seems to work in plain old pivot table.

However, this functionality is provided in PowerPivot within MsExcel, which is using the DAX expression to create "measures" (simular to calculated fields but can be manipulated with DAX functions). Normally this addin is not enabled, so you need to enable it in COM-addin.

If you are using MSOffice 2013 or above, you can enable the PowerPivot Addin in File >> Options, select COM-addin and then Go, and then use the additional functions in the PowerPivot module for your needs.

https://support.office.com/en-us/article/start-the-power-pivot-add-in-for-excel-a891a66d-36e3-43fc-81e8-fc4798f39ea8

After enabling Powerpivot addin, you can create a new measure MaxItem1 using the maxx(datatable, datatable[item1]). then you can use this MaxItem1 measure to do sum(). You may need to use sumx(datatable, datatable[MaxItem1]+datatable[MaxItem2]).

Similarly minx() can be used to create MinItem values.

You may refer to this 5-min video for how to operate in Powerpivot. This video is not actually using the sum() function. You can look into sumx() to craft that precise function as above, which is not hard.

https://www.youtube.com/watch?v=lE2OAPh-Zf8

(I dont know if quoting video is allow or not in this forum, any moderater please correct me if I did something wrong. Unfortunately the excel I am using is 2010 at the moment, and I cannot create a XLS file for you to refer to directly. But I suppose the video is pretty clear and it is very easy to do.)