5 Replies Latest reply on Apr 6, 2018 8:00 AM by Nick Dwyer

    Cannot Sum Aggregate

    Nick Dwyer

      Hi All

      This is my first post to the Tableau community forums, so please excuse if this has already been answered. I'm having some problems in Tableau 10.

      I'm trying to basically create a custom total column which sums the given "points" for performing a task, but I seem to be stopped by the pesky cannot further aggregate SUM as it's already an aggregate message. Here's the formula I have to calculate the points for the task.


      COUNT([Ticket])*SUM([Point Allotment])

      Here's a screenshot of what I'm working with, unfortunately my company DLP solution prevents me from uploading files to external sites.



      What i'd like to do, for example on 3/12, is sum the 220, 55, 41.3, and 220, but when I do the subtotal function I get the following:


        • 1. Re: Cannot Sum Aggregate
          Jim Dehner

          Hi Nick

          Welcome to the forum


          this is use a simple sales / order calculation


          like your issue if I just drag it on the viz and use grand total I get this



          but follow the path to sum and you get this





          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          • 2. Re: Cannot Sum Aggregate
            Zhouyi Zhang

            Hi, Nick


            Your calculation is somehow misleading tableau to calculate in different way which is count(tickets from BET + tickets from disconnect+ ....) * sum(points from BET+ points from disconnect+ .....),

            while in your expected result is count(ticket from bet)*sum(point from bet) + count(ticket from disconnect)*sum(point from disconnect) + ....


            So my solution is using superstore as example to mock up your situation and give the correct calculation. Below is the screenshot for your reference and a sample workbook attached as well.


            Hope this helps



            • 3. Re: Cannot Sum Aggregate
              Nick Dwyer

              Thank you Zhouyi, we're almost there. I think I should have mentioned i'm using two data sources, so I have the calculation as follows, but the error I'm getting now is "All fields in a LOD expression must come from the same data source"


              {fixed DAY([Completion_Date__c]),[Source2__c]:COUNTD([EBA_Ticket])*SUM([Goals (Production v6)].[Point Allotment])}

              • 4. Re: Cannot Sum Aggregate
                Zhouyi Zhang

                Hi, Nick


                In this case, have you tried Jim's solution?



                • 5. Re: Cannot Sum Aggregate
                  Nick Dwyer

                  Hi Jim,
                  This may be a simple solution, but here's what I'm seeing per your guidance