Join Parent & Child tables

Q&A
,
1

dataset “Invoice” (invoiceId, invoiceDate, invoiceAmt)
11,10/8/2025,100
12,11/8/2025,200

another dataset “InvoiceDetail” (invoiceId, invoiceItemId, itemAmt)
11,1,60
11,2,40
12,1,100
12,2,30
12,3,70

sum of (itemAmt) = (invoiceAmt) for each invoiceId

when join “Invoice” with “InvoiceDetail” in Quick Sight by (invoiceId), the result dataset is
(invoiceId, invoiceDate, invoiceAmt, invoiceItemId, itemAmt)
11,10/8/2025,100,1,60
11,10/8/2025,100,2,40
12,11/8/2025,200,1,100
12,11/8/2025,200,2,30
12,11/8/2025,200,3,70

we can see that (invoiceAmt) is repeating and it will be incorrect when we do direct sum(invoiceAmt) in visuals.

what is the correct approach to handle join between parent & child tables?

2

Hi @lbl

When you join a parent table (Invoice) with a child table (InvoiceDetail), repeated parent values in the join result cause aggregation issues if you sum across the joined data directly. The correct approach is to use aggregation calculated fields to avoid double-counting parent table fields like invoiceAmt.

Each parent row is repeated for every matching child, so summing invoiceAmt across the joined dataset leads to over-counting. For example, invoiceId 12 has three detail rows, so summing invoiceAmt (200) three times gives 600 instead of 200.

Try the following calculation, this ensures invoiceAmt is only counted once for each invoiceId, preventing double counting.

Example:

sum(min({invoiceAmt}, [{invoiceId]))
3

Hi @lbl,

Following up here as it’s been awhile since last communication took place on this thread; did you have any additional questions regarding your initial post?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you!

4

Hi @lbl,

Since we have not heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community.

Thank you