Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Parent/Child With SUMX

I have multiple tables that have parent/child relationships within them. I have created PATH column in each one of them and created a measure to be able to see the summed up value for the nodes as per here.

But I have a header table that can link several of the companies and label them lets say east or west in this example. I would like to see the sum of whole east node that consist of Company A and Company C. Here is the Power BI file for reference.

 

Here is the screenshot of tables and output I need. The top section contains incoming tables. Bottom left is summation within the first table as described in the forum link above. Bottom right is the table that I need but without Company Column in there and only one row for East/West.

Output I want to see: East 1050; West 600

 

Note in my modelling I won't be able to connect the Headers table to Sales table as in my real problem I have Sales table at the center surrounded with multiple Master Data with Parent/Child hierarchy in each Master Data. One Header table can not be connected to all the Master Data tables that are filtering one central Key Figure table. 

  • Anonymous's avatar
    Anonymous
    6 years ago

    Resolved by having two measures. One that filters row by row and other that does SUMX on that measure.

    Measure 3 = 
    VAR c =
        SELECTEDVALUE ( Headers[Company] )
    RETURN
        CALCULATE (
            SUM ( Sales[Sales] ),
            PATHCONTAINS ( Sales[Path], c )
        )
    Measure 4 = sumx(Headers, [Measure 3])

    File updated.

3 Replies

  • v-eachen-msft's avatar
    v-eachen-msft
    Icon for Community Support rankCommunity Support

    Hi Anonymous ,

     

    What results do you expect to receive? Like the result in that case? You could use a table to describe it.

     

    • Anonymous's avatar
      Anonymous
      Not applicable

      Updated.

      • Anonymous's avatar
        Anonymous
        Not applicable

        Resolved by having two measures. One that filters row by row and other that does SUMX on that measure.

        Measure 3 = 
        VAR c =
            SELECTEDVALUE ( Headers[Company] )
        RETURN
            CALCULATE (
                SUM ( Sales[Sales] ),
                PATHCONTAINS ( Sales[Path], c )
            )
        Measure 4 = sumx(Headers, [Measure 3])

        File updated.