Forum Discussion
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.
- Anonymous6 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
Community Support
Hi Anonymous ,
What results do you expect to receive? Like the result in that case? You could use a table to describe it.
- AnonymousNot applicable
Updated.
- AnonymousNot 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.