
1. Re: Sum Based on Group and unique category
Ratan Roy Oct 12, 2014 11:54 AM (in response to Ratan Roy) 
2. Re: Sum Based on Group and unique category
Jonathan Drummey Oct 16, 2014 7:11 AM (in response to Ratan Roy)See the attached workbook for a view that gets the desired results, here's what I did:
In order to get your calculation to compute as desired, it needs to sum up (the WINDOW_SUM) the SUM(Value)/COUNT(Category) for all Categories in both Groups. Without Group in the view as a dimension, Tableau is only applying the WINDOW_SUM to the a single set of Categories.
Here's a visual example of how you'd set up original view, I've unhidden the Category dimension and added measures to this crosstab to show each component. I'll often do this as I'm figuring out a calculation to have an understanding of exactly what Tableau is doing and what I might be missing. The SUM(Value), the COUNT(Category), SUM(Value)/COUNT(Category)  with a twopass Grand Total to show the overall sum of that, and the WINDOW_SUM(SUM(Value)/COUNT(Category)), when All is selected:
Now if I add Group to the view to Rows as a dimension, the default Compute Using of the calc is Table (Down) so that will add it to the addressing. Since each Category belongs to both Groups, we're seeing 10 Marks and the SUM(Value)/COUNT([Category]) is providing a completely different range of values to the WINDOW_SUM():
Now the final view is really only needing to show a single value. I tend to avoid using the hide marks functionality unless I've the domain (range of values) of the thing I want to hide is completely defined. For example, if a Category F was added to your data set then it would show up on the data refresh and would have to be manually hidden. Instead, I'll use table calculations to filter the data, since a table calculation filter is applied after most all computations are done.
Here's what I did: Created an optimize calc with the formula IF FIRST()==0 THEN WINDOW_SUM(SUM([Value])/Count([Category])) END. This only returns a single result for the entire partition. With a Compute Using on all the dimensions in the view (Group, Category, Calculation2) that means there's only one partition in the view (the entire result set), so the calc will only return a single value. Here's the workout worksheet:
Then I duplicated that workout worksheet, moved the Group and Category pills to the Level of Detail Shelf, turned off the tooltips for each, and Ctrl+Dragged a copy of the Calc (Optimized) calc from the Marks Card onto the Filters Shelf, this gets rid of the 9 null marks (which are necessary to get the calculation to work right) and stops Tableau from stacking the marks.
Here's the final view:
I hope this is useful, if you have any questions please let me know!
Jonathan

Unique Sum jtd.twbx 33.2 KB


3. Re: Sum Based on Group and unique category
Ratan Roy Oct 22, 2014 8:37 AM (in response to Jonathan Drummey)Hi Jonathan,
Thanks a lot.
Regards,
Ratan

4. Re: Sum Based on Group and unique category
Jonathan Drummey Oct 22, 2014 9:13 AM (in response to Ratan Roy)You're welcome!

5. Re: Sum Based on Group and unique category
Ratan Roy Nov 17, 2014 4:55 PM (in response to Jonathan Drummey)Hi All, Jonathan, Jonathan Drummey
I have one additional conditional to accomplished here.
I have to add two different calculated filed, but when i am doing it it is not giving me the proper result.
I am adding view and view1 sheet and i am trying to add both but its not working. its giving me the wrong result.
Please see and suggest.
Regards,
Ratan

Unique Sum jtd.twbx 22.6 KB


6. Re: Sum Based on Group and unique category
Jonathan Drummey Nov 18, 2014 2:14 AM (in response to Ratan Roy)What are the desired results?

7. Re: Sum Based on Group and unique category
Ratan Roy Nov 18, 2014 11:32 AM (in response to Jonathan Drummey)Hi Jonathan,
When I am selecting Base in Group filter, i should get 55 in my final view but here i am getting 15 and when i am selecting predicted , i should get 75 but its giving me 19. for All it should give a result 130.
Kindly help.
Regards,
Ratan

8. Re: Sum Based on Group and unique category
Ratan Roy Nov 19, 2014 11:01 AM (in response to Jonathan Drummey)Hi Jonathan,
Kindly Suggest.
Regards,
Ratan

9. Re: Sum Based on Group and unique category
Jonathan Drummey Nov 19, 2014 11:06 AM (in response to Ratan Roy)I'll get to it in the next couple of days, if someone else wants to take this on the meantime they are welcome to!

10. Re: Sum Based on Group and unique category
Matt Lutton Nov 19, 2014 11:34 AM (in response to Ratan Roy)Here you go, Ratan Roy. First, let me say that while it helped to know your expected results in this case, it would've helped even further (and possibly would've helped you solve the issue yourself) if you had specified the logic behind the results you were looking for.
In this scenario, you were attempting to add two measures: One was the Table Calc Jonathan came up with, and the other was SUM(Value).
This does not work, because SUM(Value) will have multiple results for the dimensions setting your level of detail. To troubleshoot your problem, here is what I did (this may help you troubleshoot in the future).
With just SUM(Value) in a view/sheet with the dimensions Group and Category on the Level of Detail, you will see this:
This helped me to identify the problem you were encountering. You were trying to add an Aggregate measure to a Table Calculation, when what we need in this case is two Table Calculations added together; again, this is because we are dealing with multiple dimensions in the view, and we need the SUM(Value) across all those Dimensions.
If you look at the "FinalViewWORKOUT" sheet, you will see how I came up with my final results. I have placed two measures in this view  one for Jonathan's Table Calc, and another is a WINDOW_SUM(SUM(Value)) measure.
The WINDOW_SUM(SUM(Value)) measure is what you needed to return a single Total Value for the selected group. The Compute Using ("addressing") is set up the same as Jonathan's calc  "Addressing" on all dimensions in the view.
The last sheet, "FINAL view", shows the results you were looking for, by adding the two Table Calculations together. In this sceenshot, I am filtered to the "Base" Group.
I hope this helps. Please let me know if you have any questions, and I would welcome Jonathan's comment or corrections as always!

Unique Sum jtd (1) ML.twbx 25.3 KB


11. Re: Sum Based on Group and unique category
Ratan Roy Nov 19, 2014 1:57 PM (in response to Matt Lutton)Hi Matthew,
Thanks a lot and thanks for the concept. its working for me.
Regards,
Ratan

12. Re: Sum Based on Group and unique category
Matt Lutton Nov 20, 2014 5:54 AM (in response to Ratan Roy)You're welcome.

13. Re: Sum Based on Group and unique category
Jonathan Drummey Nov 20, 2014 11:28 AM (in response to Matt Lutton)Thanks for taking this on, Matthew!
On Thu, Nov 20, 2014 at 8:55 AM, Matthew Lutton <

14. Re: Sum Based on Group and unique category
Matt Lutton Nov 20, 2014 11:48 AM (in response to Jonathan Drummey)Happy to do it!