Groupings & Aggregate Calculations

For this weeks community post I wanted to take a step back and talk about one of the core workflows in Sigma, Groupings and Aggregate Calculations.

A Sigma table is tremendously powerful in that we do not need to “Pivot” to group common values of a column and calculate an aggregation (aka summarize large amounts of data).

What to group

Take for example our Plugs Electronics sample data. Depending on the analysis we are looking to perform, there are tons of different columns we can use to bin (group) rows and then evaluate the sales performance at Plugs Inc including clear things like:

  • Store region (East, West, Southwest, Midwest, South)
  • Product Type (Music, Photography, Computers, Mobiles etc)

In order to add our first grouping, we can either:

A) Right click the header of the column we want to group and select “Group Column”

B) Click the + icon in the top left next to Groupings (a tooltip will show “Add Grouping”) and select the column we want

Above shown grouped by Store Region

Another SUPER common grouping would be date/time columns, which when grouped can be truncated to day, month, year, etc so that multiple rows fall under the same grouping (as opposed to date/time existing values were there are likely too many variances given the timestamp aspect).

Above shown grouped by Month

In both the above examples we can see the column used for grouping in the top right of the left side control panel, notice the date truncated to month and that both groupings are mostly collapsed using the [-] while a single is expanded using the [+] which allows us to see the underlying (aka children) rows.

Grouping Calculations

Once we have a grouping in place, we can add our aggregations!

Two ways to add an grouping aggregate calculation:

Right Click the grouping columns header, or select the + icon in the grouping menu.

Here Ive added both a Sum of profit aggregation and a Count Distinct of order ids so we know how many orders were made, and the profit for the month! Of course you can choose any of our aggregate functions such as Average and Max (see full list here).

We can also perform a calculation with our 2 aggregate columns to see something like the average profit per order (see formula bar)

Nested Grouping (sub grouping)

We can take groupings to the next level (pun intended!) by repeating our above steps with different columns. Below you can see Ive calculated total profit for the day as we did above but also grouped each days transaction by product type so we can see how the various products contributed to our profit.

This is where things get cool because we can then reference the days profit in the product type grouping level to calculate a percent of the total!

Lastly, you might be wondering how we can calculate this percent of total with a single grouping level.

This is where the Summary bar at the bottom of the table comes in to play. First click the arrow to raise it up, then the + icon to add a new value. Once we have the Total on the summary bar we can reference it like any other column, in my case as the denominator for my percent of total calculation. I should also note we JUST released a new Percent of Total formula to make this easier but for the sake of not straying off topic ill leave that for another post and just drop the documentation link here.

Hope this was helpful~


1 Like

Thanks for this post!

I don’t think there is 1 workbook I’ve worked on so far that does not use Groupings & Group calcs in some way. I do find myself frequently wanting to do window functions without having to use a grouped column. The sql equivalent is using the ‘partition by’ expression. Sometimes I want to calculate based on a partition and order without having to group columns allowing me to stay in a tabular format at the lowest level of detail in my data. Of course I can always go back to DBT to add these calcs to my source tables but curious if other users run into this need as well, or if I am already able to do so but just didn’t realize it!


Thanks for the input Blake! Not sure if youre aware but we can use the rollup function on a flat table to effectively get the grouping calculations without grouping, aka a self rollup! Documentation on the function here shows it being used as a lookup but if you simply specify the same value for arguements 3 and 4 you would get the aggregate value. EG:

A Table with line items we want the total cost for the order, instead of grouping by order ID and summing cost we can say Rollup(Sum([Cost]), [Order Id], [Order Id])

Hope this helps!

1 Like

Awesome, this is super helpful ty!