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~
BB