Need a solution to prorate values for duplicated records

[alQemay]

Please consider the following dataset: This is a result of SQL query that uses a join on
LEFT JOIN t1 on t2.parentid = t1.id

t1.parentid | t2.childid | t1. amount
A | B | 500
A | C | 500
D | E | 100

I need to prorate the amount value for records having the same parentid so that sum(amount) = 600 not 1100.
I tried using a simple calculation but encounter the error “not allowed aggregate and nonaggregate data together”.

[David_Wong]

Hi @alQemay,

You can use LAC-A functions to handle the duplicates. Take a look at use case #2 in this article.

Level-Aware Calculations: Tips and Best Practices

[Level-Aware Calculations in Amazon QuickSight] Level-Aware Calculations (LAC) are powerful functions that allow us to perform calculations at a granularity level which is different from the granularity of our datasets and/or visuals. The sample dataset referenced by this article is a dataset of product orders placed by customers. Each order can contain multiple products and each product in an order has a unique Line Item ID. Each row of our dataset represents one line item but we want to per…

[alQemay]

Yes this is exactly what I needed.

sum(amount,[t1.parentid])/Count(t2.childid, [t1.parentid])