Grouping with TOTALS row?

James Sidebotham

I have a grouping of data that I have an aggregation of (sum) for various data points (about 60 rows). Could I add a final row that provides a total for each of the aggregation columns (as seen in Excel).

Thanks

James Sidebotham

FYI, my current workaround is to send the data from the grouping and perform another sum grouping on all of the columns (not the first column of data point. I then perform a union of those two grouped datasets and this gives me what i want. But was hoping for something a bit easier / built-in. thanks

Josh Boxer

Hi James

You are correct, a Group then Union is how I would achieve this:

1. Group
2. Delete default 'Count' aggregate and add a new aggregate
3. Select 'Multiple' and set Name to blank
4. Select all (numeric) columns
5. set Aggregate to Sum and apply

—> Then Union this with the existing data

Optional, but depending on what you are doing with these totals, you could format / style the totals rows using a Transform before the Union. Example comma separating these likely large numbers and coloring the totals row to make it stand out:

SetCellStyle(

_RegexReplace(value, '(?:^|\G)(\d{1,3})(?=(\d{3})+(?:.\d+)?$)', '$1,', false),

'Success'

)

https://docs.experianaperture.io/data-quality/aperture-data-studio-v2/get-started/create-functions/#function-script-editor

James Sidebotham

I wish the cell style would persist when exporting to Excel - would be nice to keep the colours.