1 2 Previous Next 17 Replies Latest reply on Aug 7, 2018 10:10 AM by priyesh acharya

# how to ignore duplicate values in calculation

Hi All,

In order to calculate the sum of values in a column in a table, I need to include only the distinct values for another column. Can someone please point me as to how to go about doing this?

I need to understand how to create a calculated field which only takes distinct of one column in order to calculate another column.

Regards,

Priyesh Acharya

• ###### 1. Re: how to ignore duplicate values in calculation

One way is to create an LOD expression and sum that:      sum({fixed [Distinct Column] : max([measure value])})

• ###### 2. Re: how to ignore duplicate values in calculation

Thank you for your help John.

Let me elaborate the question a little bit. Please have a look at the below screenshot.

Basically what I want is that the sum or purchase for a particular customer has to include any product just once.

Can you please explain using the below example, I'm still trying to understand how LOD calculations works.

Do let me know if this helps.

Regards,

• ###### 3. Re: how to ignore duplicate values in calculation

OK so using your example the expression would be

sum({fixed [Customer],[Product Code] : max([Product Value])})

1 of 1 people found this helpful
• ###### 4. Re: how to ignore duplicate values in calculation

Hi John,

Thank you for your help so far.

Changing the requirement here a little bit, hope this clarifies things.

In this case, I need to create a field Cost/customer which should include the sum of all the unique products purchased.

So, for customer A, the cost/customer will be : X+Y+Z ie 10+11+12. It should not include X again even though it is present in the table

Similarly for customer B, the cost/customer will be: P+Q+R+S+T ( it should include R only once in the calculation).

Can you please let me know if this solution is best derived using a calculated field or Sets.

Regards,

Priyesh Acharya

• ###### 5. Re: how to ignore duplicate values in calculation

Hi Priyesh,

You should get the result by John's method. Please check this.

~Tushar

1 of 1 people found this helpful
• ###### 6. Re: how to ignore duplicate values in calculation

Hi Tushar,

Thank you for reaching out, I tried the calculation you mentioned but the answer is still not right.

Basically, every customer has multiple products which have been purchased, each product has a certain price.

I need to find the total cost per customer but using every product just once for calculating the overall cost for that customer.

If this particular product is purchased by another customer then that product is used again for calculation but just once.

Hope this clarifies my question.

Regards,

Priyesh Acharya

• ###### 7. Re: how to ignore duplicate values in calculation

Just to add, I need to show on a customer level, what is the overall cost...

• ###### 8. Re: how to ignore duplicate values in calculation

Hi Priyesh,

I modified the data as per your requirement :

and used calculation :

Unique price :{FIXED [Product] :min([Price])}

1 of 1 people found this helpful
• ###### 9. Re: how to ignore duplicate values in calculation

Thanks for looking into this Meenu but this is also giving me inflated results.

Let me try to get close to my real world data, and I do apologize to everyone for the churn here.

Every salesman, in this case Rob, Bob etc have some customers who are buying certain products. For Rob I would need to show customer level sales based on the products that particular customer bought but I have to use every product just once for that customer. So, in this case, for customer A whose saleman is Rob, the sales should be X+Y+Z(use X only once). For this same salesman, customer B's sales should be P+Q+R. For each customer I need to calculate using only unique products.

When I'm using the min function mentioned the combined value seems very high, not sure why it gets so inflated.

I want to create a calculated field by which I get the overall sales on a salesman and customer level.

Regards,

Priyesh Acharya

• ###### 10. Re: how to ignore duplicate values in calculation

Hi,

Use this:

SUM({FIXED [Salesman], [Customer], [Product]:MIN(Price)})

Trust this helps.

D

1 of 1 people found this helpful
• ###### 11. Re: how to ignore duplicate values in calculation

Hi Priyesh,

{FIXED [Salesman],[Product]: MIN([Price])}

this should work

Regards,

Soham

1 of 1 people found this helpful
• ###### 12. Re: how to ignore duplicate values in calculation

Hi Soham,

This worked but only for some salesmen, for other the value is still heavily inflated, any idea why this would be?

• ###### 13. Re: how to ignore duplicate values in calculation

Can you share a sample workbook? or at least live screenshots of the issue? It would be really helpful, as if not this thread will continue to grow with variations in the LOD

1 of 1 people found this helpful
• ###### 14. Re: how to ignore duplicate values in calculation

Hi Soham,

Wont be able to share actual workbook as this is restricted data. From what I see, its giving the right sum for certain salesmen but not for all, there are a lot of null values getting calculated.

For some salesmen, the overall sales value is correct as is the sales value at customer level. For some, at customer level is right but at overall level its wrong.

Im creating a calculated field based on what you have mentioned and using it as a sum for the salesman dimension.

1 2 Previous Next