Aggregate IF functions

1. Aggregate IF functions

Welcome back. So far, the functions we've covered have referenced a single row at a time. Now, let's talk about Aggregate If functions, conditional functions that operate on entire columns or even subsets of rows without requiring any pre-filtering. To start, let's focus on the SumIf function. Imagine that Oakmark Bank needs to capture summary metrics on their transactions table. They need a sum of the dollar value of all withdrawals, and a sum of total deposits. Eventually, they’d like to display these values as prominent KPIs on their dashboard. The TRANSACTIONS table contains both withdrawals and deposits, so how do we get a sum for each transaction type? We could create two versions of the TRANSACTIONS table, one filtered to withdrawals, another to deposits. We could then create a table summary on each table displaying the sum. This works, but there’s an easier and more efficient way using aggregate if functions. Before we illustrate this with an example, remember that a table summary is a row that appears at the bottom of a table element to show aggregate information like totals, averages or other calculations on the table as a whole. After adding a new table summary, we can create a formula in the summary with ‘SumIf’, adding [Transaction Amount] as our value and then the logic [Transaction Type]=’withdrawal’. This sums only the withdrawals, without needing to filter the table. We can repeat for deposits, and without altering our master TRANSACTIONS table. Now, we have the two metrics we need side-by-side thanks to Sigma’s aggregate SumIf logic. These table summary values can be referenced in other functions, or converted directly into KPI charts for tracking key metrics. Let’s talk about another time-saving feature of these aggregate If functions. Perhaps Oakmark has a column like we saw earlier flagging large transactions, only now it's a logical column with TRUE or FALSE in each row. Let’s pretend we want a table summary counting only large transactions. When we write COUNTIF and point to [large transaction flag], we don’t need to include ‘[large transaction flag] = TRUE’ in our syntax. The CountIf function automatically counts only the rows where the value is already TRUE. Similarly, if we wanted a table summary summing the value of transactions flagged in the large transaction column, we could simply write SumIf ([Amount], [large transaction flag], SumIf will automatically sum only rows where the logical column [large transaction flag] is true. Sigma’s many other aggregate If functions like MaxIf, MinIf and AverageIf work the same way in that they’ll always aggregate only the TRUE values in the Logical column. They save time by not requiring us to specify a false condition. One final note on these aggregate If functions. Sigma developers should think carefully about when to apply them to their data - they aren’t needed in all scenarios. For example, if you didn’t need to see all the row level detail behind your SumIf function, you could use grouped tables or pivot tables, which provide similar aggregation by collapsing underlying rows, while still allowing you to view individual records for that aggregate. Aggregate If functions are best used when row-level details are still important to keep in view.

2. Let's practice!

Let’s see some of these in practice. In the next exercises, you’ll be hands on with these powerful aggregate If functions.

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.