How the EXCEL do I calculate that in Tableau? Out of this World Expressions for Excel Users
Let’s face it: moving from calculations in Excel to those Tableau can feel a bit like steering the NSEA Protector for the first time.
It’s easy to start…then, you want to do something a bit more complicated, something you’ve done before in Excel. You try to mirror the same logic – cascading if statements, lookups, what-have-you – only to find Tableau spits in your face.
It’s time to try something a bit different.
There comes a point where Tableau starts to behave more like SQL. If you’ve come from Excel, and only Excel, then break out your Greek textbook and repeat after me: OPA!
Here’s the trick:
Most calculations work at the row level, unless they tell you otherwise.
Let’s look at this.
Done at every row
This is the mode most familiar if you’ve come from Excel, but the format of the calculation is different:
STR() makes the number a string (at the row level), so I can concatenate it with the rest. No need to spell it out with text.
We reference the field, not the cell. Don’t ask how many months I spent figuring this out…
A few things to keep in mind:
Aggregated fields do not play well with non-aggregated fields.*
There’s a ‘*’ for a massively big reason. The disclaimer is called ‘Level of Detail Expressions’ and they ARE the MotherLODE! In general though, things that are averaged, summed, or calculated so that other rows are involved do not like to play with the loner kids (AKA, the single rows).
No love for the loner kids.
If you get this message, try the following:
- If you’re trying to do something like
IF [Field]=’X’ then SUM([Numbers]) END, do this: SUM(IF [Field]=’X’ then ([Numbers]) END) - Make the other an Aggregate (MIN, MAX, AVG, and ATTR all work)
- Use Level of Detail (see below)
Break complicated calculations into pieces to troubleshoot.
You can leave them separated, if need be. There’s a couple schools of thought on this because of performance and tracking things down, but it helps sometimes to see what works and what doesn’t.
Look, ma! I split them up so I could share!
Yes, you see that scroll bar.
I used 5 calculations to make this. I could trim it, but it makes this thing even longer. Again, many different options here, some better for performance and smarter people than me could make this sing like a new NSEA Protector on a spring day. You came here…
It’s okay, I understand.
Other places to explore:
Calculation Breakdown – it’s old – but it gives a good primer for Excel users.
Google – seems silly, but sometimes you find really good examples.
Next up: window calcs.
The smart calculations Tableau gives you from the pill menu are table calcs. They do all kinds of fun things, like running sum, percent of totals, and fly to the moon. They like directions. LOTS of DIRECTIONS. Treat them like a 5-year-old. Be very clear what you want them to do and try different things if the first option doesn’t work. Good news: they’re a lot more patient (and cleaner) than the kid. Better news? Tableau 10 promises to make these things even easier. OPA!
I’ll send you to smarter people:
Tableau – the 101 on these.
Ramon’s Master List of Tableau Links – Bookmark this, send this man props on Twitter, and scroll to the part on Table Calcs. When in doubt on anything, just go there. He updates this on the regular.
Now, my favorite: Level of Detail (LOD) expressions.
Do you remember that magic moment when you discovered pivot tables? Yeah, you made 500 of them on 1 sheet and hit the autoformat button. Then you discovered GETPIVOTDATA and Excel was never the same. I’ll let you in on a secret: LOD is like pivot tables and GETPIVOTDATA. They’re magic.
People have written ballads, sonnets, and even plays on these things. And for good reason.
They’re the answer to life, the universe, and everything.
They made the life so much easier.
They’re magical.
Great, you got more balls to juggle than a circus clown…now what?
I’ll add a Part 2 at some point.
