Pivt or grouping or some other means to summarize data




I have been raking my head over this simple report since some hours now, but
finally I throw in the towel and hand it over to the experts on this forum to
guide me.

I have a spreadsheet with following columns:

Sales Region (to simplify, let us assume North, East, West, South)
Sales-id (id for "a" particular sale)
Item-Id (item that was sold in the "a" particular sales-id)
Sales price for the item

One unique sales-id can have from 1 to n item-id. The spreadsheet contains
1 line per item-id, meaning if in a sale there were 18 items sold then there
will be 18 rows in the spreadsheet with the same sales-id.

My reporting requirement is as follows:

I would prefer a drill-down approach (i.e. showing a summary level
information and when I click then I get the next level of details).

I would like to have the following summarized report

(a) Per sales-region, the number of unique sales-id and the sum of all prices.

(b) When I click on a sales-region, the selection expands to show me for the
sales-region concerned the reference of all the unique sales-id and then for
each sales id the sum of the prices.

I am able to progam the (b) part using pivot tables; but I simply cannot
manage to program the (a) part of the reporting above that I need. I have no
preference for pivot, even if a group/ungroup functionality can help achieve
it then I do not mind. But I prefer that the totals roll up or break down
depending on whether I am at sales-region level or sales-id level.




Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question