3 Replies Latest reply on Mar 20, 2014 5:36 AM by Joshua Milligan

# Sum of distinct Values

I have been struggling with a problem for over 2 days and gone through the forums for quite sometime without any success. What I am trying to achieve is in the attached excel along with the raw data. What I am tying to achieve is SUM(DISTINCT(VALUES)). I am open to even changing the data structure if it means being able to achieve the end objective.

Thanks in advance.

• ###### 1. Re: Sum of distinct Values

Liliawati,

How about the attached workbook?  I've made some assumptions but seem to be getting the same answers you are wanting.

Here's the basic idea:

Since the Min of Total Base gives the distinct value, I used WINDOW_SUM(MIN([Total Base])) to sum up all the minimum values.  I did the same thing with the count and then created another calculation to divide and get % Churn.

You'll notice I have a lot of duplicate rows (I have to have Sub Type in the view, but I don't want to show a row for each subtype).  So, I can hide the unwanted rows by using a filter on the calculated field First().

Here's how I cleaned up the view

In a production environment, I'd change the window calculations to include:

IF FIRST() == 0

THEN {Calculation here}

END

That would make it so the calculation would only be done one time (for the row I am showing).

Please take a look and see if it matches what you want and I'd be happy to answer any questions you might have.

Regards,

Joshua

• ###### 2. Re: Sum of distinct Values

Thanks a ton Joshua. This works perfectly for me. I must add that, I just blindly followed your instructions and voila!! it worked. Now understanding it properly and internalizing it is next on my agenda.

Thanks again!!

• ###### 3. Re: Sum of distinct Values

You're welcome!  Definitely let me know if you have any quesitons.

-Joshua