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

Cannot Sum Aggregate

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

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

Jim

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

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

ZZ

• 3. Re: Cannot Sum Aggregate

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

Hi, Nick

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

ZZ

• 5. Re: Cannot Sum Aggregate

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

Thanks

Nick