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

    Sum of distinct Values

    Liliawati Sukamto

      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
          Joshua Milligan



          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}



          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.




          • 2. Re: Sum of distinct Values
            Liliawati Sukamto

            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
              Joshua Milligan

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