7 Replies Latest reply on Feb 13, 2012 5:21 AM by Alex Kerin

# Distinct sum based on another column

Hi,

I have tried searching for this and while I have found similar solutions, unfortunately I have not found anything to help my specific issue.  If there is an answer to this elsewhere, please let me know

OK let's see if I can explain this:

Customer Number
Parent Customer Number
Parent Customer Sales Volume

1

10\$50,000
211\$100,000
399\$60,000
410\$50,000
511\$100,000
688\$50,000

Basically, the total unique sum of Parent Customer Sales Volume should equal \$260,000.  Parent Customer Number repeats in my set of data, and that is the column I need to sum my Sales Volume based on.

I am running Tableau 7 Personal - using an Access database as my data source.  I am fairly new to this so apoligies in advance for seemingly obvious questions.

Edit: I had thought about adding a new column into my data to identify unique parent customer numbers once with simply a "1", and then sumif based on that value.  I just haven't though about how I could do that just yet, even in excel.

Any help would be much appreciated!

Gareth

• ###### 1. Re: Distinct sum based on another column

Gareth,

I can help you with the second half of your question. In Excel first sort your data ascending on 'Parent Customer Number'. Then create a column off to the right named something like: 'Is Duplicate?'. Starting in the second row put in this formula:

When you drag copy it down you get this:

Any duplicate gets a 0, non-duplicates are 1. Only works if you remember to sort first.

(Also don't forget to put a 1 in the first row.)

--Shawn

• ###### 2. Re: Distinct sum based on another column

Shawn,

That's a really good idea, thanks for that.  I think I can achieve what I need doing it this way - it's fairly messing from the aspect of when my data updates I need to ensure this does too, but it's probably by far the simplest way - and simple is usually always better

Thanks!

• ###### 3. Re: Distinct sum based on another column

this can definitely be done in tab - prob with a table calc like window_min but difficult to say without seeing the structure of your data - is it exactly the same as above?

• ###### 4. Re: Distinct sum based on another column

Alex, I'm curious to see your solution. I worked on it a bit and could not figure out how to do it in Tableau. So just for a bit of learning, let's assume the structure is as presented. How do you get around the duplication issues without a key?

Thanks,

Shawn

• ###### 5. Re: Distinct sum based on another column

See attached - to get the sum I used the window sum of attr - attr can only have one value as the data here does, so just uses this for the sum. It is computed using the parent ID. Don't know how robust this would be.

1 of 1 people found this helpful
• ###### 6. Re: Distinct sum based on another column

Interesting. I didn't know how ATTR worked. Good to know. Unfortunately, when you drop customer ID on shelf the dupes come back in, and Grand Total turns into an *. So you're right probably not too robust. But thanks for the lesson.

--Shawn

• ###### 7. Re: Distinct sum based on another column

I don't think there's going to be any way to show the customerIDs and have a grand total that doesn't have the dupes or a *. You would have to mash together the two sheets on a dashboard. You can't manipulate the grand total function at all.