Summarizing distinct data

[vkedia]

Greetings,

Lets say I have a dataset with records of transactions, with each transaction having a unique ID. Now, in this dataset, these IDs are actually duplicated because a transaction can involve the purchase of multiple products. However, the way my dataset works is that the total amount of the transaction is recorded for each product. For example, if a transaction had 3 products and the product costs were, 10, 12, and 15, for these records, the amount column will not show their individual prices but rather the total transaction cost which would be 37 in this case. So the problem is that when I try to get my total sales amount, there is a duplication of data. Can I mitigate this in quicksight by selecting only one amount in case a transaction id is recorded multiple times?

The following is a basic representation of what my dataset looks like, the values in the amount column are duplicated, in reality the numbers 37 and 50 are divided into the 3 products but the dataset considers the full amount in each transaction id. Is it possible to consider only one of these values for each transaction id using calculated fields? I have been able to achieve this through Power BI’s SUMMARIZE function.

Thank You!

transaction_id	product_id	amount
1	A	37
1	B	37
1	C	37
2	D	50
2	E	50

[prantika_sinha]

Hi @vkedia ,

Please try to calculate sum of amount / count of product at transaction id level and then sum it up.

You can try something like :
sum(amount,[{transaction id}])/distinct_count({product id},[{transaction id}])

Thanks,
Prantika

[vkedia]

Thank you so much for your response, but this isn’t giving the correct value. I think it’s because some of my transactions are unique with only one product, while others have 2,3, etc. and its not able to account for these different values. Right now my solution is to add another dataset which is summarized at the transaction ID level via a Custom SQL. The problem with the approach is my dataset is already extremely large, and adding yet another table will only increase the storage, therefore, any alternatives are welcome

[Xclipse]

**Hi @vkedi, besides using custom SQL – I’m out of ideas. Check out some of the functions. Also, try using the Amazon Q Assistant on the function documentation page.

More features are being added on a regular basis, so please keep an eye on the What’s New / Blog . You can set up a Watching Alert by clicking on the bell icon.

Functions by category - Amazon QuickSight