0
Is there some way to provide your own values for the grouping rows in a pivot table. I have data from an external system with values provided for all drilldown levels, laid out as rows. There is one column per aggregation level and the sublevels are blank for rows which provide aggregate values.
An trivial example is a 2 level drilldown per region and country where we ahve the columns
Region; Country; Value
We might have the two rows
Europe; Germany; 10
Europe; France; 12
and the aggregate row
Europe; (blank); 25
(the value is not additive, so not simply the sum of the two previous rows)
The problem is now that if I try to createa drilldown report in excel, the pivot table functionality simply interprets the blank entry as another entry on the most granular level and then sums over the rows to provide a total for Europe. The resulting total will be 10 + 12 + 25 instead of just 25.
Is there some way of presenting such "pre-aggregated" data in a drilldown report in excel?
Thanks, Rickard
Thank you. This solves the first half of the problem. The next step would be to be able to use the blanks as aggregates, rather than letting the pivot table calculate the aggregates. This I suspect is not trivial or even possible... – Rickard – 2011-10-18T13:40:38.103