Lennart has attended:
Excel PowerPivot course
Non-hierarchical Pivot Tables
Hi,
I am trying to make a Pivot table where the rows are based on different columns. However, there is no hierarchical connection between those columns. I want the pivot to be displayed as a table, so without any hierarchy between the columns.
For example, we have a list of companies who make products, say cars, toys and soap. However, some companies produce multiple products (Comp A produces cars and soap). From each company we also have their net value.
Company Car Toys Soap Net Value
A Yes Yes $10
B Yes $50
C Yes Yes $20
D Yes $40
Now, I want to make a pivot table giving the summed up net value of companies per product:
Net Value
Car $30
Toys $70
Soap $50
So there is no hierarchy between the Car, Toys and Soap. We do have a lot of metadata (such as country, region etc) which is why we want to use pivot tables instead of just excel formulas.
Hopefully someone can help!
Lennart
RE: Non-hierarchical Pivot Tables
Apologies, the formatting got messed up. Basically:
Company A makes Cars and Soap ($10)
Company B makes Toys ($50)
Company C makes Cars and Toys ($20)
Company D makes Soap ($40)
The Net value for
Cars ($10 + $20) = $30
Toys ($50 + $20) = $70
Soap ($10 + $40) = $50
RE: Non-hierarchical Pivot Tables
Hi Lennart
Thanks for your question.
Usually to summaries net values by product you would need to arrange your data in a different way. It is called normalizing.
COMPANY PROD NET VALUE
Company A Cars $10.00
Company A Soap $10.00
Company B Toys $50.00
Company C Cars $20.00
Company C Toys $20.00
Company D Soap $40.00
This may not be practical to do but if it can be arranged this way then a PivotTable can easily sum the Net Values.
Please let me know if the format of your table is fixed or can it can be rearranged and I'll get back to you.
Regards
Doug Dunn
STL
