Power View: Material Recipe / Product Hierarchy

[claytonbrown23]

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

 

[scottsen]

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...

 

[PentaGalCXO]

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.