2 Replies Latest reply on Jun 24, 2016 4:56 AM by Ricard Kelly

# Need help on distinct sum

I have a scenario here,

I have two columns one is the unique volume and the other is unique sum, some how I can make Unique volume but struggling with unique sum. In the attached excel work book sheet 'Non Unique' is non unique values and in the second sheet 'Unique' I have all unique values. The Non unique rows of Jobid is 950 and the unique job id is 792 and the sum of unique Job Gross Amount when calculating in tableau is taking duplicates in to consideration which  is not correct.

Any help in this is much appreciated.

Regards,

Bala

• ###### 1. Re: Need help on distinct sum

Hi Bala,

You can use the following calculation to apply the SUM function to a single value for [Job Gross Amount] from each [Job ID]:

SUM({ FIXED [Job ID] : MAX([Job Gross Amount]) })

As all values of [Job Gross Amount] for each [Job ID] are the same, you can use any function that selects just one from them.  I've used MAX, but you could use the MIN function, the MEDIAN function, etc.

The FIXED level of detail expression, running over the dimension [Job ID] will then take the value of the MAX function and produce a list of values, one for each [Job ID].

The function SUM will perform the step of adding the list of values together.

Hope that helps.

Ricard

1 of 1 people found this helpful
• ###### 2. Re: Need help on distinct sum

Alternatively, you will want to use the INCLUDE level-of-detail expression instead if you need to report sums for different time periods or broken down by other dimensions.

`SUM({ INCLUDE [Job ID] : MAX([Job Gross Amount]) })`
1 of 1 people found this helpful