Column Total Greater than Sum of ROWS

jtrollinger
jtrollinger Member
in Beast Mode

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

image.png

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • ArborRose
    ArborRose Coach

    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.

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • jtrollinger
    jtrollinger Member

    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
    jtrollinger Member

    If I count without Distinct then my number is incorrect

  • MichelleH
    MichelleH Coach

    @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
    ArborRose Coach
    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.

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • marcel_luthi
    marcel_luthi Coach

    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)

Categories