4 Replies Latest reply on Jul 7, 2016 12:28 PM by Sunyang Fu

# How to Select the Unique Row for Table Calculation

I want to do a table calculation on the following table to calculate the UNIQUE sum of the column 'Amount'. The uniqueness is defined by the Hospital Name, Date and Amount. In the example below, I want to calculate the sum(Amount) filtered as the highlighted green. If the row is not uniquely defined by Hospital Name, Date and Amount, will be treated as duplicated value, and will not be added to the calculation. Could anyone help me with that? Thanks!

In this case, the result should be: 4520+3025+5710+3465+...+3635

• ###### 1. Re: How to Select the Unique Row for Table Calculation

Hi Sunyang

Fun problem

The way I did it (which may not be best practice..., interested to see how others solve it)

First I created a unique field (hospital, date, amount)

Unique Record = STR([Hospital])+STR([Date])+STR([Amount])

Then to use this new field for the amounts, so

IF COUNTD([Unique Record])=1 THEN ATTR([Amount]) ELSE NULL END

The only problem is I cant seem to return the SUM in Tableau?! (its been a long day!)

Hope that helps!

Cheers

Mark

• ###### 2. Re: How to Select the Unique Row for Table Calculation

I don't have time to do data entry based on a screenshot and I can't open Mark twbx so I duplicated a few rows of superstore data to demonstrate a slightly different method with LOD.

1. Convert sales to a dimension, duplicate sales and convert the duplicate back to a measure.

2.  Create an LOD DiDooped Sales:  { FIXED [Customer ID], [Order Date], [Sales] : MIN([Sales (copy)]) }

This should eliminate the duplicate rows from the calculation.

1 of 1 people found this helpful
• ###### 3. Re: How to Select the Unique Row for Table Calculation

Thanks Mark! The Unique Record method was pretty smart! Check the Fixed function Ivan suggested, I was able to get the sum value

Best,

Sunyang

• ###### 4. Re: How to Select the Unique Row for Table Calculation

Hi Ivan, thanks for the help! The Fixed method works like a charm

Best,

Sunyang