4 Replies Latest reply on Jun 9, 2016 6:24 PM by Lian Norliansyah

# Removing duplicates (shared members) when calculating top of hieararchy

Hi,

I would like to create a report which user can drill down and drill up (using filter - show relevant values).

The issue is that there are duplicates members at the bottom level.

The objectives is that when user select the top of the hierarchy, it should only show the sum for the

top of the hierarchy after removing the duplicates (shared members).

Here I attached an illustration of what I want to achieve.

Please advise how to do it, as I'm still new to Tableau and lots to learn.

• ###### 1. Re: Removing duplicates (shared members) when calculating top of hieararchy

Hi Lian

Please have a look at the attached workbook.

Instead of value I have used Countd(Group)

when you show the totals you get the total values you are looking for.

I'm sure this will help you get some way towards solving the issue.

Let me know if it helps or if we need further tweaks to get to your desired result

• ###### 2. Re: Removing duplicates (shared members) when calculating top of hieararchy

Hi Khalid,

Count only works if all values are 1, however, in real case, the value is not always 1.

I updated the example by changing "Triple Shared Group 3" value to 3.

In this example, I expect Sub Dept B value to be 12, and Dept BC value to be 8.

If using count, the value would be 10 and 6.

I attached the updated workbook samples.

Regards,

Lian

• ###### 3. Re: Removing duplicates (shared members) when calculating top of hieararchy

Unfortunately I am unable to reach the desired result.

The best I could get was the total value using the formula

(Value) / {Fixed [Group] : count([Group]) }

which gives you the total on the l1 Team level as it splits the value of the shared groups .

So if shared group 3 appears 3 times and the value is 3

each ones value will be calculated as 1.

If you do manage to get to the desired result please share.

Would be interesting to see how it can be achieved if at all.

2 of 2 people found this helpful
• ###### 4. Re: Removing duplicates (shared members) when calculating top of hieararchy

Hi Khalid and Swaroop,

Thanks a lot for your help.

You have helped me to get to the right direction in getting the right answers.

This is fantastic.

I found that when I just need to show the Value with the team as filter,

I can use the following formula, which will count the unique value of the [Group]

{FIXED [Group]:min([Value])}

Regards,

Lian