Power View: Material Recipe / Product Hierarchy

claytonbrown23

New Member
Joined
Oct 26, 2014
Messages
1
Hello,

I'm using Excel 2013, and windows 7. I've spent a good 4 or 5 hours Googling this and can't figure it out so I thought I'd try MrExcel!

I'm new to Power Pivot / View and am trying to figure out how I can "teach" excel the relationship that exists between my data tables as it relates to a Book of Materials, or when you make several products - but sometimes one product is part of another. The below data is what I'm working with, I'm trying to learn Power Pivot (hence why the data is ridiculously simple!).

PRODUCT -- PARTS
Phone -- Small Screen, CPU
Tablet -- Big Screen, CPU
Laptop -- Big Screen, CPU, Keyboard
CPU -- CPU

PARTS -- SUB-PARTS
Small Screen -- Glass
Big Screen -- Glass x 2
Keyboard -- Plastic


A few things to note:
  • I sell some products without modifying them (CPU's)
  • Not all products have the same # of parts (Laptop = 3, Phone / Tablet = 2).
  • Each product and part would also include LABOR and DEPRECIATION

The End Result / What I'm Looking For:

I'd like to have a stacked bar chart that I'd choose a product to display. So, I'd choose phone and see a stacked bar chart with 3 sections - Parts, Labor, and Depreciation.

If I'm curious about where the cost of parts is coming from I'd double click the parts section and get a new stacked bar chart that would have 2 sections - Small Screen, and CPU.

Being curious as a cat I'd then double click "Small Screen" to see where that price is coming from and I'd get 3 sections - Parts, Labor, and Depreciation (yes ... again).

And Finally I click on Parts and get a solid, 1-bar stacked chart with glass.

This is quite the headache because I don't know where to even start. It's hard to think of how I can possible "explain" to excel this relationship ... even though it's quite simple.

I started by organizing my data in 2 tables. One table with a product / part / sub-part in one column and the destination in the other column. The other table just had Labor and Depreciation for each product or part. Because the first table has repeating values in both columns it is difficult to figure out how to teach excel that 'to find the parts total for Phone, you need to find all instances of "phone" in the SECOND column and add totals for the items in the corresponding rows of the FIRST column'.

Any help would be greatly appreciated!

Thanks,

Clayton
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Thanks for your well formulated and interesting question. I wish I had more time to help this week, ha! :)

My gut is you end up looking at some part/child relationships: Understanding Functions for Parent-Child Hierarchies in DAX

Or not. that "glass belongs to 2 different parts" thing makes this all pretty challenging, and you may need some "many 2 many" patterns, which are tough for beginners. (and intermediates, and some pros :))

Optimize Many-to-Many Calculation in DAX with SUMMARIZE and Cross Table Filtering – SQLBI

You have certainly chosen a first problem that is a rough one... :(
 
Upvote 0
I agree with scottsen on all his points (as usual). I avoid Many-to-Many if at all possible. In this case I would look hard at doing "row expansion" of your source data. For instance in your sales data, for every record of Laptop sales, expand that record into 3 records that represent the 3 sub components. Do the same for all products that have sub-components. If you are importing this data into PowerPivot via SQL, this could be just a matter of an outer join between your sales table and a product hierarchy table. Doing so gives you a Fact table at the sub-component level, which eliminates the need for many-to-many and greatly simplifies your data model & DAX equations. Hardest part is crafting the outer join. After that its bread & butter PowerPivot.
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top