If you’re coming from Excel, Tableau may feel like a blessing and a curse. On the one hand, it automates and simplifies in a few clicks what could take hours in Excel. On the other, it uses a different calculation logic and requires you to think about data as aggregates rather than individual cells.
When I was first learning Tableau, one of the most difficult adjustments to make was to aggregate calculations based on multiple conditions. For example, I wanted to use SUMIFs and COUNTIFs to get a single output value. While single-output calculations like this are Excel’s bread & butter, Tableau thinks about them differently.
In short, you can SUMIF in Tableau’s calculated fields with an IF statement in the form “IF [DIMENSION]>x THEN SUM([Measure])” or using a FIXED level of detail expression in the form “{ FIXED DIMENSION1, DIMENSION2, … : SUM([Measure]) }”.
The purpose of this article is to show you two ways to perform SUMIF and other aggregation calculations in Tableau, and explain how Tableau processes these requests differently than Excel. We will use Tableau’s Bookshop database that you can download here:
Download the Excel database
Download the Tableau workbook
From Excel to Tableau
To understand SUMIF and other conditional aggregations in Tableau, read this article. To learn everything else you need to work fast, effective, and impressively in Tableau after Excel, get on the waiting list for our eBook.
Two Ways to Execute SUMIFs in Tableau
There are two ways to perform SUMIF in Tableau: (1) IF calculated fields, and (2) level of detail (LOD) calculated fields (such as FIXED, INCLUDE, EXCLUDE). LODs are less intuitive but more powerful when mastered, and they’re critical to advancing in Tableau.
IF statements are similar to the SUMIF Excel formula, but LODs are an entirely different way of processing information.
It’s important to keep in mind that these are both examples of calculated fields. What is a calculated field in Tableau? It’s just what it sounds like — a new column in the underlying database that you create using a function.
Calculated fields are fundamentally different from Excel formulas. While in Excel a calculation always occurs within one cell regardless of its placement in the worksheet, calculated fields are always a computation applied to every cell in a new column of the underlying data table.
Let’s look at an example of IF calculated fields and LODs to understand better.
IF Example
Let’s use the following data set from Tableau’s Bookshop data. This is a chart of the USD sales of the top 10 books sold in Q1 of a random year.
Let’s say we’re only interested in sales from books that were released in the second half of the month. Our theory is that these books typically perform worse because people get paid at the beginning of the month and therefore have more money to focus on books.
To calculate this in Excel, we would simply use the SUMIF formula to sum the values within the January column whose Day of Publication is equal to or greater than 15. In Tableau, we could create the following calculated field:
When we include this calculation as a measure in our view, we see only the sales values of books that were sold in the second half of the month. Then we can simply add a column grand total to the view by going to Analysis > Totals > Show Column Grand Totals. We could also remove the publication dates and book details to leave us with one cell that consists of the grand total, but this is less insightful. Our table then looks like the following:
As you can see, rather than create a single output cell for the aggregation like we would in Excel, we’re telling Tableau to look at each of the criteria in our view, as well as the criteria in our calculated formula, and return the value we’ve requested given all of these criteria. Cool huh? Make sure you get your name on the list for other skills:
LOD (FIXED) Example
Rather than using IF statements, we can use a special level of detail formula called FIXED. In short, FIXED allows us to create an aggregate calculation based on a set of dimensions. Unlike the SUM IF formula, FIXED ignores any filters in our view, so we need to enter all relevant dimensions in the formula directly.
Let’s imagine that we want to sum the sales of book prices in January, but only show the sum of values at the FORMAT level of detail. This way we can compare each individual book’s sales to its format at large. In our example it would look like the following calculation:
We could specify down to the same granular detail in our view, but in this case we’re looking to compare the individual book sales with its format category. The view would look like this:
Now we have two values of book sales that are the results of SUM IF calculations! Each book is calculated using a IF formula, and each format category sum is calculated using a FIXED level of detail aggregation formula. Pretty cool!
Conditions on Dimensions in LOD
Unfortunately, it is not possible to add conditions directly in LOD dimensions, so we cannot add the H2 of the month calculation, DAY([Publication Month])>=15, directly in the FIXED formula. You can only SUM on the condition of an entire column in LODs. Make sure you get your name on the list for our Excel to Tableau eBook:
Two Ways to Perform COUNTIF in Tableau
COUNTIF works the same way as SUM IF in Tableau. We can use IF statements to count the number of records that meet a specific condition, or default to a level of detail calculation with COUNT.
In our example above imagine that we simply want to count the number of books sold in H2 of a month. In this case, we only need to change our formula to reflect a COUNT of the [Price]. In this case, we’re using a separate [# of books] multiplier since a COUNT of price would only give us 1 value per book (its MSRP), rather than the total number of books sold in H2.
In addition, we could count the number of books sold in each format category by modifying our FIXED LOD to COUNT ISBN Sales Q1 aggregation on format and month of sales date:
How to Perform IF SUM in Tableau
Similar to SUMIF is the situation in which we want to execute a calculation when the sum of a first calculation meets certain conditions.
For example, we may want to count the number of books who sales meet a minimum threshold. We can use the same two functions to do this: IF statements and LOD calculations.
Revisiting our example, let’s COUNT books whose January sales are greater than 1,500. We would modify our formula in the following way:
In our fields, [Sales] is already a multiplication aggregate, which just represents a SUM() in a different way. The principle remains: if the sum of Sales is more than 1,500, then the book gets counted.
Likewise, imagine we want to count the number of books in format categories whose total sales exceeds 1,500. We can write the following LOD function to retrieve these values:
Other Aggregates
I hope this article has helped the you the near-infinite conditional aggregation possibilities in Tableau. We’ve looked at SUMIF, COUNTIF, and IFSUM examples, but you can substitute all possible aggregations: averages, minimum, maximum, and count distinct.
At the end of the day, you should be able to perform any kind of analysis with simple IF statements and FIXED level of detail calculations. For a more comprehensive guide on switching from Excel to Tableau, get on the waitlist for our guide:
If you liked this article, check out more data, finance, and business analyst content on the AnalystAnswers.com home page!
