Smart totals in aggregate models

To say a little more about what I mean – and what I tried – I have three related objects’ data I would like to include in the aggregation. These three objects are hierarchically related as a grandparent, parent, and child object (grandparent and parent via lookup relationship, parent and child via master-detail relationship). I have a formula field on the grandparent object (Contact in this case) called Contact Count, where the formula = 1 to get an unduplicated count of contacts in report sums.

In my Skuid page, I based an aggregate model on the child object, with a table component displaying the aggregate data. I am grouping by a field on the parent object and filtering by a field value on the child object. I would like to aggregate data from all three objects, including getting an unduplicated count of grandparent records (contacts in this example). When I sum on Contact Count, however, I am getting the duplicated sum, i.e., the number of child records per group and total column summary, rather than the unduplicated count of contacts per group and total for the report. Is there a way to get unduplicated aggregate values with Skuid?

See screen shots, and apologies for all the redacting – had to hide the group names, but they are in the same order in the two examples. Ideally, I would be able to get the same values for Contact Count in Skuid that I am getting in the Salesforce report. Thanks for any help with this!

Thank you, Peter!  I don’t know how I missed the Count Distinct function, but that worked, at least for the unduplicated count within each group.  I can’t use the table column summary to get an unduplicated sum at the bottom, but I would be able to get that using count distinct on a separate model that has the same conditions, but doesn’t have the groupings.  Or is there a way to have an unduplicated total count for a given model rather than a sum of the groups?

Seems like this will work for counts, but not for other summary functions.  For example, in that same report, I would not be able to accurately calculate the average age of contacts per group, since contacts with multiple children and grandchildren would be counted multiple times.  Is there a way to get unduplicated totals for other types of summary functions besides count?

Here are two more advanced methods. 

1. I’m assuming that a count of distinct contacts becomes the same as a “row count” on the report.  You can use the sytnax  {{$Model.ModelName.data.length}} to provide that value and it will update as you filter and interact with the table. 

2. Getting a true average when you have indeterminate rows in your children and grandchildren objects is going to be really tough.  if you can guarantee that either the number of grandchildren per child are the same,  or if the value is repeated on every record - the “average” function gives you the correct number at the parent aggregation.   But I don’t think that works for you…

This might be a good place for a roll up summary field? 

There are more intense means of getting what you are after - but I’ll leave those for another day…  Do these items help?