
1. Re: Sum of distinct Values
Joshua Milligan Mar 19, 2014 9:37 PM (in response to Liliawati Sukamto)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

Sum of Distinct Values.twbx 26.4 KB


2. Re: Sum of distinct Values
Liliawati Sukamto Mar 20, 2014 3:02 AM (in response to Joshua Milligan)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 Mar 20, 2014 5:36 AM (in response to Liliawati Sukamto)You're welcome! Definitely let me know if you have any quesitons.
Joshua