Pivot Table Guru needed. How to summarize a parts qty and total cost sorted by customer name.

This question is only for a true Pivot Table Guru. I am already pretty good at Pivot Tables, but this one problem is beyond my abilities.

My current Pivot Table creates subtotals customer orders and shows the quantity of each part.

I also want it to show the price and total value (qty * price).    But, I want it displayed inthe very particular way which is shown on the attached screen shot.  

Can any Guru show me how to do this fancy stuff with Pivot Tables without ugly workarounds.

For instance, I already know a bunch of workarounds to get my desired result:
I could copy the table and manually adjust it.
I could write a macro to automate those manual steps.
I could show quantity * price as a "side by side" field using options > calculations > calculated fields.
And, I found some other workarounds here
But, I am hoping someone can show a more elegant solution


Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Nope - PivotTables are pretty inflexible with regard to header and what rows they display.

With regard to the latter, a more elegant solution would be to write a macro to hide the rows you don't want - or why not roll your own, and do your table not as a PivotTable, but with formulas. This way, you can customise it however you want.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rberkeConsultantAuthor Commented:
I have been "rolling my with formulas" for many years, but recently I have been using pivot tables to consolidate multiple regions from multiple worksheets.

The particular layout from this question has come up a half dozen times in the last few months, which prompted me to ask this question.  

I have written a macro which does what I want.  Now, I just create the pivot table summarizing quantities,  then call my macro which copies the pivot table to another location and extends it. The resulting table has links, so that refreshes to quantities in the underlying pivot table are reflected in the copy.  (changes to prices and part numbers would require rerunning my macro).
I have attached it if anyone is interested.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.