Results 1 to 3 of 3

Thread: Power View: Material Recipe / Product Hierarchy
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Oct 2014
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Power View: Material Recipe / Product Hierarchy

    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

  2. #2
    Board Regular scottsen's Avatar
    Join Date
    Mar 2014
    Location
    Seattle, WA
    Posts
    1,263
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power View: Material Recipe / Product Hierarchy

    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...
    scott at tinylizard.com : Power Pivot Consultant, Trainer and Mercenary
    See my Power Pivot blog at http://tinylizard.com/blog

  3. #3
    New Member
    Join Date
    Jan 2012
    Location
    Houston TX
    Posts
    46
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power View: Material Recipe / Product Hierarchy

    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •