Parent Child Hierarchy Totals and Aggregations

[LohitDacha]

Hey Everyone - 
I have the following scenario I need solving and I hoping someone could direct me in how to achieve this. 
I have parent order and child order with their relevant totals in the below formats. 

Most of the time, parent and child are the same. Sometimes, they tag warranty orders in the child with a .1W incrementally. 

For the ones which have warranty orders, they would like to sum up the total cost of the order itself and  all the warranty orders to the parent. 
They would also like to see a separate Warranty Cost on the same line which is the sum of all .1W rows. Any ideas on how to achieve this? 

Parent	Child	Totals	Cost + Warranty (new Column)	Warranty(New Column)
A	A	100	200	100
A	A.1W	20
A	A.2W	30
A	A.3W	50

[DataNinja777]

Hi @LohitDacha ,

You can achieve this result in Power BI by using a combination of a DAX calculated column to establish the parent-child relationship and then a couple of measures to perform the aggregations. This approach is efficient and keeps your model clean.

First, you'll need to add a calculated column to your table (let's call your table Orders) to create a consistent ParentID. This column will extract the base order number, ensuring that orders like AA, AA.1W, and AA.2W are all grouped under the parent AA. You can add this column using the following DAX expression:

ParentID = 
VAR DotPosition = FIND(".", Orders[ParentChild], 1, 0)
RETURN
IF(
    DotPosition > 0, 
    LEFT(Orders[ParentChild], DotPosition - 1), 
    Orders[ParentChild]
)

Once you have the ParentID column, the aggregations can be handled by two separate measures. The first measure will calculate the total combined cost for the parent and all its children. Because of how Power BI's filter context works, a simple SUM is all you need. The second measure will calculate the sum of only the warranty orders by using the CALCULATE function to apply a filter.

Here are the two DAX measures:

Aggregated Cost = SUM(Orders[Cost])

Warranty Cost = 
CALCULATE(
    SUM(Orders[Cost]),
    CONTAINSSTRING(Orders[ParentChild], ".W")
)

To see the final result, you can create a table or matrix visual in your report. Drag your new ParentID calculated column onto the rows. Then, drag both the Aggregated Cost and Warranty Cost measures into the values field. Power BI will automatically calculate the correct totals for each parent group, giving you the exact summary you're looking for.

Best regards,

[johnbasha33 · Accepted solution]

Hi @LohitDacha 

SQL-Based Solution

Assuming a table called Orders with columns: Parent, Child, Total

SELECT
Parent,
MAX(CASE WHEN Parent = Child THEN Total ELSE 0 END) AS OriginalCost,
SUM(Total) AS [Cost + Warranty],
SUM(CASE WHEN Child LIKE '%.%W' THEN Total ELSE 0 END) AS Warranty
FROM Orders
GROUP BY Parent

Power BI (DAX)

Create two measures:

CostWithWarranty =
CALCULATE(SUM(Orders[Total]), ALLEXCEPT(Orders, Orders[Parent]))

WarrantyOnly =
CALCULATE(SUM(Orders[Total]),
FILTER(Orders, CONTAINSSTRING(Orders[Child], ".") && RIGHT(Orders[Child],1) = "W"))

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

[LohitDacha]

Thank you so much @johnbasha33 This worked like a charm. 
I do see one more issue - the total in the bottom of the visual gives me a very random number. This total should be approx 2M, but gives me a total of 105M. I do not think it is filtering the total based on the visual's filter. Any suggestions?