How to Make Tableau Histograms with Aggregate Calculated Fields

Finally View Value Distributions Even When Measures are Ratios

By default, Tableau histograms cannot be used with calculated fields such as Profit Ratio (i.e. Profit divided by Sales). In this video, learn how to make a default histogram with two clicks – plus a trick for making histograms with aggregate calculations.

Hi. This is Ryan with Playfair Data TV. And in this video, I’m going to show you not only how to make a histogram, but how to make a histogram with aggregate calculated fields.

Over here in Tableau Desktop, we’re going to use the Sample – Superstore dataset to demonstrate. Histograms are one of the few chart types that I prefer to create with the Show Me feature because, essentially, if you’ve got any measure, you click Show Me, and you can make a histogram. That’s how simple it is. That’s why I wanted to use this opportunity to show you a slightly more complex way to make a histogram.

But first, for the foundation, like I said, if I just click a measure and choose Show Me, one of the options that I can create is a histogram. It’s here on the second-to-last row. And it’s the second option. You can see the criteria there to create a histogram. Simply try one measure.

One unique thing about histograms, as you can see in the description below the requirements to draw this chart type, is it says it creates a bin field. And it also says it’s not available for all measures. But Profit– it looks like we could use a histogram. So let’s go ahead and click that and see what we end up with.

What it means when it says it creates a bin field is when you create a histogram, you’ll see a new field show up. And it’ll have the name of the measure, followed by the word “bin” in parentheses. By default, Tableau will pick the size of those “bins.” I think of those as buckets of values.

Let’s take a closer look at what Tableau added to the Rows and Columns Shelf. Maybe this will make a little bit more sense. When I created the histogram, it added our Profit measure to the y-axis with an aggregation of COUNT. What it’s doing is it’s counting the number of values in these different profit bins. And that’s why you see this on the Columns Shelf, creating our x-axis.

Well, you can edit the size of those bins by right-clicking on the field and clicking Edit. So far, it’s giving us a suggested bin size of 283. That’s not a very human-friendly number or breaking point. I might change this to something more round.

If we’re dealing with profit values, maybe I’ll start out with trying around 100 and click OK. And we should see more bins show up, and we do. And if I hover over one of these, what that number represents– it’s saying that we’ve got 7,233 profit values in the Sample – Superstore dataset between $0 and $100. The next bin up will be between $100 and $200. Next bin up– between $200 and $300, and so on– and you can edit that yourself.

I’m going to undo just to clean my slate. So that’s how to create a histogram– great option for showing distributions in your dataset. However, they don’t work with every measure, as we saw in that description. Let’s try Profit Ratio, which also comes with the Sample – Superstore dataset.

If I go over here to Show Me, notice this time that the histogram is grayed out. I’m not able to create a histogram with an aggregate calculated field. But it would really be nice to know the distribution of my profit ratios across my dataset. I wish I could create a histogram.

I’m going to share a quick tip that allows you to do that. All you have to do is make a calculated field that uses a FIXED Level of Detail expression to create both the numerator and the denominator. The formula for Profit Ratio is SUM of Profit divided by SUM of Sales. I’m going to fix both of those at the Order ID level. And then I’ll be able to make a histogram.

I’ll call this Profit Ratio for Histogram or With LOD, however you need to name it to help you find it later on. This will start with an open curly bracket. It’s going to be a FIXED Level of Detail expression at the Order ID level because I want to look at my distributions or my counts by Order ID. That’s as granular as I’m getting– so FIXED Order ID, colon, SUM of Profit. That’s the numerator for Profit Ratio.

I will then divide that by the denominator, which, again, requires a FIXED Level of Detail expression at the Order ID dimension. And we’re dividing it by SUM of Sales– close parentheses, close curly bracket.

After we have fixed both the numerator and the denominator– so it’s the same formula, profit divided by sales. The difference is I’ve added a Level of Detail expression to fix both the numerator and denominator at the Order ID level. I click OK on that.

Now if I choose Profit Ratio for Histogram, I do see histogram show up. And now I can look at my distributions by a aggregate calculated field. Just like before, we see a new bin show up as well. Maybe for profit ratio, I want these increments to be in 5 percentage points.

So I’ll edit the bin. Right now, they’re at 7 and 1/2 percentage points. I’ll bump that down to 5– .05, which means 5%. We should see more show up. I’ll show the labels. And now we have a new insight.

We can see things such as our highest distribution of profit ratios is between 25% and 30%. We had 698 Order IDs that had a profit ratio between 25% and 30%. We were only able to do that with this quick tip I just shared, which allows you to make histograms out of aggregate calculated fields.

This has been Ryan with Playfair Data TV – thanks for watching!