3 Ways to Normalize Data in Tableau

Now, let’s take a look at the parameter control for this visualization. Since we’re only concerned with viewing the data either in its original format or normalized, we can set the data type to Boolean. This gives us two options, and I’ve also set my aliases as “On” and “Off” to make it even easier in a dashboard.

From here, we drop Game ID into the Columns shelf and our Calculated Field onto the Rows shelf twice, then right click the second pill to select “Dual Axis.” After that, make sure one of your measures in the Marks card is listed as a Circle data type, and the other is a Line data type. This gives us the basic formatting, showing each game, the winning and losing scores, and the margin of victory/defeat for the Chiefs.

Changing the Normalization parameter to the “On” position gives us the losing score set at zero for each game, making it easy to see which games were close contests!

How to calculate a min-max scaling in Tableau

Indexed normalization, or sometimes called “min-max normalization,” is a way to standardize measures that vary widely in scale to a single standard. Perhaps you have sales figures from multiple regions that vary widely in volume and want to bring all the regions into the same scale. In doing so with min-max scaling, you are setting a range for your data of [0,1] where 0 is the minimum and 1 is the maximum.

The formula for this is:

While we used a sports-related dataset for the first normalization method, we’re going to use the built-in Sample–Superstore dataset for the next two methods. Before we visualize, we will need to create our min-max calculation. Using the formula above, our min-maxed Profit formula should look like this:

To best visualize how the min-max normalization changes the scale of our measure, I’m going to create a box-and-whisker chart for each. Start by dragging both our Profit and Min Max Profit measures to the Rows shelf, and add Region to the Columns shelf. Next, add Subcategory to the Detail property of the Marks card to give us the level of detail we want to see in our measure. Then, using the “Show Me” menu in the top right corner of your page, select the box plot icon to generate the view below.

As you can see, both charts look the same, however if you look closely at the y-axis on both charts, the scale has been normalized between 0 and 1 on the Profit Min Max version. Going forward, you could compare other measures like Sales or Orders to Profit, all on the same 0-to-1 scale. Furthermore, if you are building a data model for advanced analysis, normalizing all scales can help you avoid outliers in one measure from distorting the model too heavily.

Normalizing per capita data in Tableau

Our third method of adjusting our data to a common scale will be to normalize data by population size. In the Sample-Superstore dataset, you can see that California and New York have the largest amount of profit… but is that because they like the products the most or simply because they have the largest populations? Normalizing per capita is a better way to analyze this dataset, so before we go any further, we need to download and join population information to the Sample-Superstore dataset. I grabbed a simple table from the US Census website and joined it to Sample-Superstore.

For this third example, we’re also going to use the same parameter we created in the first example. This will let us toggle between the raw profit numbers and the profit normalized by state population.

A quick LOD Calculated Field to make sure we’re using the right state population, and then we have a Profit Normalization calculation that should look familiar from example 1 above.

Now, Tableau has a built-in map chart type you can select from the Show Me menu on the top right of your screen. When you add our measures to the Marks card, your map should look like this:

And when you switch the Normalization toggle to the ON position, it should look like this:

With Sales on the Size mark and Profit on the Color mark, you can quickly see the difference. Look at Delaware’s per capita sales and profit!

How to Deduplicate Joined Rows in Tableau

I hope taking a look at these three ways to normalize your data was helpful to you.

Thanks for your time!
-Matt