Different aggregation functions for different dimensions in Excel pivot table

1
votes

Can I define different aggregation methods for subtotals in different dimension in an Excel pivot table?

The following example shows a result I'm trying to obtain. The metric to aggregate is, let's say, lines of code of a software project. The 2 dimensions in question are Date and Organization. In source data, Organization is broken down into 2 columns, Department and Project, while Date is a single column and Excel makes up the Months/Years summaries automatically when making the ODBC data connection.

enter image description here

A metric such as this one should be aggregated differently along the different dimensions. For the Organization dimension, the subtotal for all projects of the department is the SUM, but in the date dimension, the subtotal for all months of the year is the MAX of any given month (or perhaps AVG, or last etc. but certainly not SUM).

I've tried to define the different aggregation methods in Excel in the field settings, but it always selects one or the other method for both dimensions. Is there a way to do it, preferably using standard Pivot Table mechanisms or at worst a UDF in Excel?

1
excelpivot-table
I'm sorry if I don't understand. Your sample data seems to be using MAX(), not SUM()? - Charles Merriam
It's MAX() in the horizontal dimension, but SUM() in the vertical - that's the whole point. - ttarchala

1 Answers

1
votes

What I would do to tackle this problem is to add both aggregation functions: sum and max , then hide ( or shrink a lot ) those columns you do not want to display.

example of what the resulting pivot

in the above example I shrink columns B,D,F and I because of they has values that are out of scope for your requirements.

The "Total Max of Loc" displays a value consistent with the function expressed throughout the entire column: that is "the maximum number of lines of code reached by each project in each department; this could lead to misunderstandings when we observe the values of the subtotals and grand total; i.e: The "Grand Total - Total Max of Loc" is not the "Total Max of Sum of Loc": in the example, it shows 18 which represents the absolute maximum value of Loc in a Project in each Department; In the same way the Total Max of Loc for Department 2 is 18 and form Department 1 is 12

When requested a different behavior as expressed in comment to this answer, I think we are entering into the strong customizations space and some solution could be found by writing custom macro and by leveraging the getpivotdata function or, if it can be acceptable for your case, simply by the addition of a new column with the max()formula and possibly hiding the column "Total Max of Loc"

custom solution