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

Improve this question

1 Answer 1

Reset to default
1

You can exclude blanks from the result by using the drop down menu on Row Labels but there is a trick. The contents of the drop down are dependent on what cell is currently active in the pivot table. Make sure that one of the Country names is selected, or '(blank)', and then open the drop down menu. You can deselect (blank) and it will be removed from the aggregates.

Improve this answer
1
  • 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
    Commented Oct 18, 2011 at 13:40

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .