Calculations and aggregations not reflecting accurately

Question & Answer
,
1

I have a dataset and I want to aggregate client summary for the sum of Total Amount.
Attached is a picture of my sample dataset.

image
image977×151 5.17 KB

This is the result I want :
image

I want to aggregate the result based on the distinct chat id but when I try to do that, I get an error from quicksight
image

Can I get any suggestions using LAC-W/ LAC-A on how to do this?

2

Hi Prajakta,

Thanks for reaching out. Rather than excel can you please also provide screenshot with mock dataset and calculation you are using in QuickSight, that is giving you wrong result . Prima facie seems you have to wrap min() function around LAC-A calculation you are using in QuickSight for total_amount to make it work.

Regards,
Anwar Ali

3

Hi Anwar, sorry for taking so long to get back.

Here is the result I’m trying to achieve is this:

image
image797×151 10.2 KB

but the total amount is getting counted twice due to the message ids from the table. Here, I’m just summing total amount (sum{total_amount}) for every dealer store.

Here is the actual dataset. For the same conversation id, the amount is counted multiple times resulting in duplicates.

image
image1222×387 32.2 KB

But the actual result should be this- The total amount for ABC Sports should be 85k and Mango Tires should be 122k
image

4

Hello @Prajakta and @AnwarAli !

@Prajakta were you able to find a solution or work around for this issue or do you still need help? @AnwarAli was the context and screenshots above helpful?

If I understand your use case my recommendation is to do something like this:

sumOver(distinct_count({message_id}), [{client_name}])

Let me know if that helps!

5

Hi @duncan , I’m still trying to figure out the best approach for this problem, thanks for your help!

6

Hello @AnwarAli and @Prajakta!

No problem @Prajakta! Did my suggestion above not work and are there anything new things that you have tried?