Column Total Greater than Sum of ROWS

jtrollinger

Why is "# Houses" Column Total Greater than Sum of ROWS on "Closed Punch Tickets by Plumber" Card

BEAST MODE for "# of Houses":

COUNT(DISTINCT(CASE WHEN JT158_WTParent = 'Y' AND HdrParentItemCode = 'ZPUNCH' THEN SalesOrderNo END))

GrantSmith

Likely, you have multiple records for the same SalesOrderNo value so when it's doing it in aggregate it's doing the distinct count across all superintendents instead of adding each individual distinct count per individual superintendent.

GrantSmith

Likely, you have multiple records for the same SalesOrderNo value so when it's doing it in aggregate it's doing the distinct count across all superintendents instead of adding each individual distinct count per individual superintendent.

ArborRose

I too think your DISTINCT is probably the reason. If you are trying to count, I would create the CASE statement to be COUNT(CASE WHEN {conditions met} THEN 1 ELSE 0 END)….without DISTINCT.

jtrollinger

This does not make sense. How can ROW Totals for Michael Reed on Closed Punch Tickets by Super Card match Total of Closed Tickets by Plumber then. Both Cards are pulling from exact same data set, filters are exactly the same on both Cards but Closed Tickets by Plumber Card is just showing what Plumbers have same Superintendent for those Houses?

jtrollinger

If I count without Distinct then my number is incorrect

MichelleH

@jtrollinger Total rows calculate based off of values in the source dataset, not the aggregated values in the table rows. In this case it means that there were 47 unique values in the SalesOrderNo field, some which have records for multiple Assigned Plumbers.

ArborRose

https://community-forums.domo.com/main/discussion/comment/86437#Comment_86437

Michelle is correct. In fact, I am staring at one of my own cards where the grand total shown is completely wrong because of this reason.

marcel_luthi

As @MichelleH explained this is the default behavior and often what you want, specially as this will be responsive and dynamic to any level of aggregation you need to have in place.

If for some reason you do not want the Total Rollup to be the COUNT DISTINCT for the full data table, then you'll need to change your aggregation to be a Beast Mode, using FIXED at the level displayed on your card and then indicate to then SUM those at any higher level (which means you need to know what the level of granularity will be at which you want the count UNIQUE to happen). Something like:

SUM(COUNT(DISTINCT (CASE WHEN JT158_WTParent = 'Y' AND HdrParentItemCode = 'ZPUNCH' THEN SalesOrderNo END)) FIXED (BY `Assigned Plumber`))

Basically asking the system to tell you the number of unique houses each plumber visited and then add those up. Or another option is to do a COUNT UNIQUE on the concatenation of the Plumber + SalesOrderNo:

COUNT(DISTINCT(CASE WHEN JT158_WTParent = 'Y' AND HdrParentItemCode = 'ZPUNCH' THEN CONCAT(`Assigned Plumber`, SalesOrderNo) END))

But again it all depends on what the data is meant to show and COUNT DISTINCT the way you have it is most likely the accurate representation the business might be looking at (they just need to understand that depending on the level of granularity the same order might appear repeated multiple times)