8 Replies Latest reply on Mar 14, 2018 5:37 AM by Zhouyi Zhang

# Dividing one value by the sum of unique values

Hi all

I have a dataset in which I am tracking the ownership of some household objects. Some objects are owned by more than one person so they have been entered in multiple rows into the dataset with one row for each person (including a field on the percentage of the object they own).

What I am trying to do is create a calculation so I can show of the total value of the household objects, how much of the value are the owners linked to.

For example:

John is involved in the ownership of a \$1500 PC and a \$500 TV but not in the \$2000 dining suite. The total value of the items is \$4000 and John is involved in the ownership of \$2000 worth of those goods so he has a 50% stake in the total value.

I've tried creating a calculated field to do this calculation which I thought would be relatively simple: [value]/sum([value]). However I can't get that formula to work and I need to sum of the entire column to only count each item once. For example if the Dining Suite which is valued at \$2000 is owned by four people, it is entered into the sheet four times so when I sum the value it comes at \$8000 instead of \$2000.

However I can't seem to get it to get it to work unfortunately. I've attached a packaged workbook which has a subset of the data.

• ###### 1. Re: Dividing one value by the sum of unique values

Hi, Tom

Can you tell what's the individual share amount measure?

ZZ

1 of 1 people found this helpful
• ###### 2. Re: Dividing one value by the sum of unique values

Hi Zhouyi

Yes th eindividual share measure can be displayed as a number (2 decimal points).

1 of 1 people found this helpful
• ###### 3. Re: Dividing one value by the sum of unique values

Hi, Tom

Please find my solution attached as well as screenshot below

Hope this helps

ZZ

1 of 2 people found this helpful
• ###### 4. Re: Dividing one value by the sum of unique values

Hi Shouyi, that is not actually what I am after.

What I want is a calculation of how much of the total value of items that each individual is linked to. The ownership share should not be involve din the calculation. For example:

The total value of items here is \$724,000. Jack is involved in the ownership of the Dining Set (\$2000), the House (\$500,000) and the PC (\$1500). The total value of items Jack is linked to is \$503,500. This means he is linked to 69.54% of the total value of items.

Does this make more sense? That is the calculation I am after but need to find a way to automate it for the entire dataset.

• ###### 5. Re: Dividing one value by the sum of unique values

Hi, Tom

like this?

ZZ

2 of 2 people found this helpful
• ###### 6. Re: Dividing one value by the sum of unique values

Zhouyi you are a wizard! That is exactly what I was after. Can you care to explain how the formula works to produce the correct answer? I still don't quite understand it.

Many thanks

Tom

• ###### 7. Re: Dividing one value by the sum of unique values

Thanks for posting this, i was about to add a similar post. My one has some slight complexities though in that I want to be able to have the result recalculate if I filter what is shown based on a category.

I've attached an example workbook in which your calculations worked initially. But if i want to then filter out one category (say remove blue items from my example) the current solution doesn't recalculate to exclude the values which have been filtered out. Is there a way to do this?

• ###### 8. Re: Dividing one value by the sum of unique values

Hi, Tom

The first part is easy to understand, sum up the total value for a person

The 2nd part is, as your data structure, the value per item is duplicated, so I need use average first, and then sum them up.