14 Replies Latest reply on Nov 20, 2014 11:48 AM by Matt Lutton

    Sum Based on Group and unique category

    Ratan Roy

      Hi All,Jonathan Drummey

       

      i am trying to calculate sum based on Group and unique category. Each group have duplicates category and its value. I have to calculate the sum of unique category.

       

      I am able to calculate it separately, i.e when i am selecting Base  and Predicted separately in filter the correct sum is  coming. But when i am selecting "All" then o/p is not correct.

       

      What i have done here is i have created one calculated field - Calculation1 : WINDOW_SUM(SUM([Value])/Count([Category]))

      and drag this to number card and make it compute using table down. Now here i have value for all category, i have hidden all category except one and make it Sum. Attached is the workbook for your reference.

       

      For base the sum value is 11 and for predicted it is 15. When i select All, it should be 26.

       

      please help how to calculate it as i know i know when i select All it taking the avg value.

       

      And is there any other workaround to do this..?

       

      I have attached the data and Workbook for your reference.

       

      Regards,

      Ratan

        • 1. Re: Sum Based on Group and unique category
          Ratan Roy

          Hi All,Jonathan Drummey

           

          Could you please help me on this.

           

          Regards,

          Ratan

          • 2. Re: Sum Based on Group and unique category
            Jonathan Drummey

            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 two-pass Grand Total to show the overall sum of that, and the WINDOW_SUM(SUM(Value)/COUNT(Category)), when All is selected:

             

            2014-10-16 10_00_23-Tableau - Unique Sum.png

             

            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():

             

            2014-10-16 10_09_23-Tableau - Unique Sum.png

             

            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:

             

            2014-10-16 10_04_23-Tableau - Unique Sum.png

             

            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:

             

            2014-10-16 10_06_18-Tableau - Unique Sum.png

             

            I hope this is useful, if you have any questions please let me know!

             

            Jonathan

            • 3. Re: Sum Based on Group and unique category
              Ratan Roy

              Hi Jonathan,

               

              Thanks a lot.

               

              Regards,

              Ratan

              • 5. Re: Sum Based on Group and unique category
                Ratan Roy

                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

                • 6. Re: Sum Based on Group and unique category
                  Jonathan Drummey

                  What are the desired results?

                  • 7. Re: Sum Based on Group and unique category
                    Ratan Roy

                    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

                      Hi Jonathan,

                       

                      Kindly Suggest.

                       

                      Regards,

                      Ratan

                      • 9. Re: Sum Based on Group and unique category
                        Jonathan Drummey

                        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

                          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:

                           

                          SumValue.png

                          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.

                          Final View.png

                          I hope this helps.  Please let me know if you have any questions, and I would welcome Jonathan's comment or corrections as always!

                          1 of 1 people found this helpful
                          • 11. Re: Sum Based on Group and unique category
                            Ratan Roy

                            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

                              You're welcome.    

                              • 13. Re: Sum Based on Group and unique category
                                Jonathan Drummey

                                Thanks for taking this on, Matthew!

                                 

                                On Thu, Nov 20, 2014 at 8:55 AM, Matthew Lutton <