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?