Relational Database Calculation over several Layers

[awolf88]

Dear community, 

I'm in a bit of a pickle once again and could use some of your collective, genius brain power: 

I have two tables, one for Sales

and one for Components:

Now your good eye on the components table might have already spotted my problem: The way the Database is setup, some "Parts" in the "BaseArticles" have sub-components and are therefore listed twice, once in Parts and Once in BaseArticles. For some items, in our example "Spring" - is even part of a 3rd Layer. So i.e. there are 3 Springs in every Cylinder, 4 Cylinders in every Motor and 1 Motor in every Car. 

The struggle I'm having is for my PowerBI Calculation to grab all the components throughout all layers, if that makes sense. So if my sales table looks like the above, I'm trying to achieve this quantitave result and am not sure of how to achieve this: 

I've gotten so far in PowerBI as I normally would with calculations, but it only seems to be grabbing the total Qty of Parts from Layer 1. I've attached the demo file for reference. 

https://drive.google.com/file/d/1Aq2FSfwSntU1b8u1OCLBuvZyiWrcmWAg/view?usp=sharing

Thanks for all your input in advance as always.

Appreciate your brain power!

Best regards,

Alex

[Anonymous]

Hi @awolf88 ,

I give my thoughts for your reference. 
Assuming that the car has a total of three levels, if there are more levels, there are additional steps to continue to determine which item belongs to.

Create a calculated column.

Item = 
VAR _var_1 =
    IF (
        CONTAINS (
            'Components Table',
            'Components_Database'[Parts], 'Components_Database'[BaseArticle]
        ),
        'Components_Database'[BaseArticle]
    )
VAR _var_2 =
    CALCULATE (
        MAX ( 'Components_Database'[BaseArticle] ),
        FILTER ( 'Components_Database', 'Components_Database'[Parts] = _var_1 )
    )
VAR _var_3 =
    CALCULATE (
        MAX ( 'Components_Database'[BaseArticle] ),
        FILTER ( 'Components_Database', 'Components_Database'[Parts] = _var_2 )
    )
VAR _var_4 =
    IF ( _var_2 = "Car", "Car", _var_3 )
VAR _var_5 =
    IF ( ISBLANK ( _var_1 ), 'Components_Database'[BaseArticle], _var_4 )
RETURN
    _var_5

 Change the relationship. 

Create another calculated column.

Quantity = RELATED('Sales Table'[Quantity])*'Components_Database'[Qty Parts in Art]

Attached PBIX file for reference.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[awolf88]

Hi Gao!

Thank you so much for your input! Your concept truly works for my problem with one final request though: My original Data-Set does have a few more layers than 3 as you say. I am aware of how to adapt it as needed. My last problem to the solution though is that I have many more BaseArticles with layers than "Car". So I would need to adjust your "_var_4" line coding slightly. Since my Data-set contains more than 100 Lines, i can't type out the name of the BaseArticle individually if that makes sense. 

I've tried changing your _var_4 line as follows, but without success:

Item = 
VAR _var_1 =
    IF(
        CONTAINS(
            'Components Table',
            Components_Database[Parts], Components_Database[BaseArticle]
        ),
        Components_Database[BaseArticle]
    )
VAR _var_2 =
    CALCULATE(
        MAX( Components_Database[BaseArticle] ),
        FILTER( Components_Database, Components_Database[Parts] = _var_1 )
    )
VAR _var_3 =
    CALCULATE(
        MAX(Components_Database[BaseArticle] ),
        FILTER( 'Components_Database', 'Components_Database'[Parts] = _var_2 )
    )
VAR _var_4 =
    IF(_var_2 = Components_Database[BaseArticle], Components_Database[BaseArticle], _var_3 )
VAR _var_5 =
    IF( ISBLANK( _var_1), Components_Database[BaseArticle], _var_4 )
RETURN
    _var_5

 the result then shows:

Do you have an idea for a dynamic approach for _var_4?

Thank you so so much for your time and knowledge. Really appreciate you helping me out!

Best regards,

Alex

[Anonymous · Accepted solution]

Hi Alex,

In this case the formula becomes more complex and unmaintainable. 
Is there a way to create an intermediate table that reflects the relationship of each part to the top level? Like this.

Then modify the relationship.

You will get results quickly.

Attached PBIX file for reference.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[awolf88]

Hi @danextian ,

appreciate the fast response!

Let me try to elaborate what I'm after:

Based on the sales table (quantities sold), i would like to show a total amount list of Components (Part) that are sold within. So my goal would be to display the orange table (Sales by Components) in the end. The difficulty I'm having is the layering. 

Hope that makes sense. 

Best,

Alex

[danextian]

Hi @awolf88,

I'm trying to understand your explanation but can't quite get it. So for example, for car, what value/number do you expect based on the screenshots in your post and what is the breakdown/how did you come up with that value?