6 Replies Latest reply on May 16, 2018 4:26 PM by Eva K

# sum of unique amount for the same id

I really need help here as I believe the calculation I am using is correct, but its not working correctly.

I have a dataset with the same id on multiple rows, there is an amt column as well, i want to find a max(amt) for unique id and then summarize that for the entire table.

here's my sample data: so, all the highlighted are the max(amt) and their sum is 1400

This is the calculation i am using in tableau: {include [Id1], [Id2], [Id3]: MAX([Amt])}

and then summing this calculation.

I also tried this way: {include [Id1]: MAX([Amt])}

both ways, giving me all the rows and not just the unique id1 rows' amount.

Can someone help me? i have attached the twbx file.

• ###### 1. Re: sum of unique amount for the same id

{FIXED id1:MAX(amt)}

This will return Max for each Id1

and you want to Sum

then

{FIXED:SUM({FIXED id1:MAX(amt)})}

• ###### 2. Re: sum of unique amount for the same id

Ok, i tried your solution on my sample workbook, it works there but it doesn'

t work on my actual workbook. Unfortunately i cant share it here as it has restricted data.

here's the screenshot of my 'real' data and the calculated field.

and i am dropping this calculated field on the Text card as a 'Sum'

what am i missing? And thank you so much for your help.

• ###### 3. Re: sum of unique amount for the same id

{FIXED:SUM({FIXED id1:MAX( If  THIS= "a" OR THIS="b" OR THIS="c" THEN amt END)})}

• ###### 4. Re: sum of unique amount for the same id

no luck

• ###### 5. Re: sum of unique amount for the same id

Please create a fake data  similar to your actual data and let me know what do you want in one line please

• ###### 6. Re: sum of unique amount for the same id

Measure1: sum(amt) for unique id1

and

measure2: sum(amt) for unique id1 with stateid in ('AT', 'AS','RE')

twbx is attached with the fake dataset.