Power View: Material Recipe / Product Hierarchy


New Member
Oct 26, 2014

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

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

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!



Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December


Well-known Member
Mar 16, 2014
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... :(


New Member
Jan 31, 2012
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.

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...