Dynamically Control Formatting Using Multiple Calculations

In this blog post, I focus on a technique where you create multiple measures using calculated fields, to control the formatting of a single measure.  What do I mean by this?  Well, assume you are showing the sum of sales in a BAN.  For large numbers in the millions, you may want to show the BAN in display units of M with one decimal point, $2.3 M for example.  However, what if you allow the user to filter the data and that number is reduced to a very small number, say $42,000?  Presenting this figure in the same format would yield $0.0 M.  This is a very common obstacle when building interactive dashboards. 

The following blog post will provide you with a solution to this problem and five other related problems (plus a bonus).  Please note that this is not a new technique.  This technique has been around for a long time and I am not necessarily doing anything innovative.  I personally don’t know the origin of this trick, but if you do, please let me know and I will add a prominent link to this blog post. Thank you to the people that created and have shared this technique in the past. That said, this problem arises frequently on social media and on the Tableau forums (it’s clearly a common issue), so I decided that it would be helpful to simply put it down on paper. 

Before moving forward, please download the associated Tableau workbook, which I will reference throughout this blog post.  The header of each section will correspond with each worksheet in the workbook.  Be sure to read the captions at the bottom of each worksheet. 

Throughout the blog post, we will employ the same general technique, just in several different ways.  The technique requires the use of multiple calculated fields.  We will use an IF statement in each of them and in each case, only one calculated field will result in a value and the others will result in a NULL.  This will allow us to place multiple fields on the view, each with different formatting, but only one will show at a time.  Okay, that may have sounded a bit confusing, so let’s get into a couple examples. 

Change Formatting in BAN

Let’s first address the example noted in the first paragraph above.  Let’s assume you have a BAN containing a number in the millions and you have formatted the BAN to be shown with one decimal place in display units of M.  On the “Change Formatting in BAN” worksheet in the associated workbook, you will see $2.3 M as a BAN.  As previously mentioned, this becomes a problem when you start filtering the data and a figure like $42,000 becomes $0.0 M.  So how do we address this?  Well, we will create two new calculations (note that all of my calculations will preceded by a @ symbol):

          @Sales in K

                   IF SUM([Sales]) < 1000000 THEN SUM([Sales])

ELSE NULL

END

This calculation evaluates our BAN (sum of sales) to determine how large it is.  If it is less than $1 million, then it will yield the sum of sales.  However, if it is equal to or greater than $1 million, it will result in a NULL value.  (It is not necessary to include the “ELSE NULL” syntax, but I wanted to explicitly include it in this blog post and will do so throughout).  To be clear, the result will either be the sum of sales or a null, depending on the sum of sales value. 

Let’s create a second calculation:

          @Sales in M

                   IF SUM([Sales]) >= 1000000 THEN SUM([Sales])

ELSE NULL

END

This calculation will do the opposite.  If the value is greater than or equal to $1 million, it will return the sum of sales; otherwise, it will return a NULL. 

You’ll see that the two calculations are doing the opposite thing.  To show this, I’ve included a simple table.  No matter the value of the BAN, one of the calculations will show SUM(Sales) and one will show a NULL.

We now need to set the default properties / formatting for each measure.  In the list of measures on the left, right-click on “@Sales in K” and choose Default Properties, then Number Format.  Set the value to Currency (Custom) with zero decimal places and Display Units of K.  Do the same thing for the “@Sales in M” measure, but set it to Currency (Custom) with one decimal place and Display Units of M. 

Now, let’s build our view.  (In my example, I created a calculated field filter with just two values in order to allow us to see the changes more easily.  I’ve placed this on the filter shelf and showed the filter).  Place both @Sales in K and @Sales in M on the Text Card.  Click on the Text Card and click “…” to the right of the word “Text”.  This will bring up a new window.  Your two calculations should be showing on two separate lines.  Place them on the same line, next to each other, with no spaces in between.  In my example, I also added the word “SALES” below it.  It should look similar to what is shown below.  Click OK.

<AGG(@Sales in K)><AGG(@Sales in M)>

SALES

When you have finished, you should see a BAN of $2.3 M.  It is only showing the value of one measure, but in reality, there are two measures.  Since the figure is larger than $1 million, the @Sales in M figure is displayed with its default formatting and the @Sales in K is NULL, therefore not being displayed.  Now uncheck the “1. Before 2019” box on the filter.  The total figure is reduced to below $1 million.  Because of this, the @Sales in K figure is displayed with its default formatting and the @Sales in M is NULL.  The figure now shows as $733 K. 

Change Color in Tooltip

In this example, we want to add color to the tooltip – red for when the value is below zero and blue for when the value is greater than zero.  Unfortunately, when applying color to a tooltip, the color is static, it cannot be changed dynamically.  To resolve this issue, we can create two calculations as we did above where one is always NULL and one is always the measure then color them accordingly. 

To understand what we are trying to accomplish, go to the “Change Color in Tooltip” worksheet and hover over the bars.  You’ll see that negative profit figures are red in the tooltip where positive figures are blue. 

As before, create two calculations:

          @Profit Negative

                   IF SUM([Profit]) < 0 THEN SUM([Profit])

ELSE NULL

END

This calculation will yield the sum of profit when the sum of profit is less than zero and a NULL when it is not.

Create a second calculation:

                  

          @Profit Positive

                   IF SUM([Profit]) >= 0 THEN SUM([Profit])

ELSE NULL

END

This calculation will do the opposite – it will yield the sum of profit when the sum of profit is greater than or equal to zero and a null when it is less. 

Add both calculations to the Tooltip card.  Like before, they will display on different lines.  You will add them to the same line with no spaces between them.  Change the @Profit Negative measure to red and the @Profit Positive measure to blue.  It should look like the following:

<Order ID>

<AGG(@Profit Negative)><AGG(@Profit Positive)>

Again, one calculation will be NULL at any given time, so when you hover over the bars, only one will show – red for negative numbers and blue for positive numbers.

ASCII Characters in Tooltip

This example is similar to the previous one in that we will utilize two calculations in a tooltip where each will employ a different color based on a value.  Please see the corresponding tab in the workbook, which measures the quarter over quarter change.  Hover over the bars.  For quarter over quarter decreases, you will see a red down arrow and for increases, you’ll see a blue up arrow. 

In the viz, I placed Order Date on Rows and set it to discrete Quarter.  I then placed SUM(Sales) on Columns.  In order to calculate the quarter over quarter change, I created a calculation called: @Q over Q Change, which equaled SUM([Sales]) - LOOKUP(SUM([Sales]), -1 and set it to compute using Order Date.  I then placed this calculation on the Tooltip card.

Okay, now to create the up and down arrow calculations – this will be very similar to what we did above:

@Arrow Up

          IF [@Q over Q Change] >= 0 THEN '▲'

ELSE NULL

END

          @Arrow Down

                   IF [@Q over Q Change] < 0 THEN '▼'

ELSE NULL

END

When the Q over Q Change is zero or positive, the first calculation will yield an up arrow and the second calculation will be NULL.  When the Q over Q Change is negative, then the first calculation will be NULL and the second calculation will yield a down arrow. 

Place both of these fields on the Tooltip card.  Like in previous examples, place the two arrow calculations next to each other with no spaces between them and change the colors to blue and red accordingly. I’ve also added some other details to the tooltip as well.

<QUARTER(Order Date)>

<SUM(Sales)>

<AGG(@Arrow Up)><AGG(@Arrow Down)> <AGG(@Q over Q Change)>

Now when you hover over the bars, you will see red down arrows and blue up arrows based on whether the values increased or decreased.

Swap Measure with Parameter

This particular example came up on Twitter recently.  Someone needed to swap measures using a parameter, but in doing so also required different formatting for each.  In this example, we will use Profit and Profit Ratio, which will be shown in dollars and as a percentage respectively. 

Before we talk about how to build it, check out the “Swap Measure with Parameter” worksheet.  Use the parameter to choose different values and see how the chart changes as well as the formatting. 

To create this, first create a parameter and call it “Profit or Profit Ratio”.  Set it to String and List then add values of Profit and Profit Ratio.  Show that on your worksheet. 

Now create your first calculation, which will result in either some value or a NULL:

@Swap Profit

          IF [Profit or Profit Ratio] = 'Profit' THEN [Profit]

ELSE NULL

END

When the parameter is set to Profit, then the result of this calculation will be Profit.  When set to any other value, it will be NULL.  Set the default number formatting to currency with two decimals. 

Now create a second calculation:

         

          @Swap Profit Ratio

                   IF [Profit or Profit Ratio] = 'Profit Ratio' THEN [Profit Ratio]

ELSE NULL

END

When the parameter is set to Profit Ratio, then the result of this calculation will be Profit Ratio.  When set to any other value, it will be NULL.  Set the default number formatting to percentage with one decimal.   

Now, place Order ID on Rows (I’ve filtered my Order IDs to just about a dozen of them).  On Columns, place @Swap Profit and @Swap Profit Ratio next to each other.  Change them to a dual axis and synchronize the axes. 

On the @Swap Profit axis, place @Swap Profit on Text and remove anything else that might be on the cards (except measure values if it shows on color).  On the @Swap Profit Ratio axis, place @Swap Profit Ratio on Text and remove anything else that might be on the cards.

Now use the parameter to toggle between Profit and Profit Ratio.  When you do so, one of the calculations will be a NULL.  This means that the bars will show only one measure with its associated label.  Toggle and you will see bars change in length, the labels will change, and the formatting of the labels will change. 

Tooltip for BANs with Precision

One technique that I love to employ in my daily work is to provide additional information related to BANs.  More specifically, I like to allow users to hover over a BAN to see the full, unformatted amount.  An example of this is shown in the “Tooltip for BANs with Precision” worksheet.  You’ll see $2.3 M as a BAN but if you hover over, you will see the full amount of $2,297,201.  This technique is a bit different from the others, but in the same vein. 

In this case, I just used the standard Sales measure and formatted it as currency using one decimal place and display units of M.  I then created a secondary calculation “@Sales Full Number” and simply made it equal to Sales.  I then formatted this as currency with no decimal places. 

I placed Sales on Text for the BAN then placed “@Sales Full Number” in the tooltip.  With some formatting, you have a nice BAN with additional information provided on hover. 

Auto Change Formats in Table

Move to the “Auto Change Formats in Table” worksheet.  This view shows a table of sales by region which will automatically be formatted according to the data in the view.  You’ll see that it is currently showing as one decimal in display units of M.  However, change the date filter to only include a single month.  You’ll see that the formatting automatically changes to the full number (no decimals) with no display units.  Now check all twelve months of 2016.  It this case, it is automatically to be displayed in units of K.

How does this work?  As you probably guessed, it uses three different calculations, one for each type of formatting.  Each calculation evaluates the smallest number in the view and the formatting is determined based on that value.  For example, if the smallest number in the view is $120,000, it would utilize the K formatting and the other two measures would be NULL.  If the smallest number was $12 million, then it would utilize the M formatting. 

Before building this, I created a new calculation called @Fake Sales, which was equal to Sales * 10.  The only reason I did this is that the sales figures in the Superstore data set were too small to properly introduce this technique. 

Now let’s build it.  We will create three calculations.  Each calculation will evaluate the smallest number in the view using Window_Min and will return the @Fake Sales figure or NULL.  Only one calculation will return the @Fake Sales figure at any given time (two will always be NULL).  Finally, all three measures will be formatted differently. 

          @Fake Sales None

                   IF WINDOW_MIN(SUM([@Fake Sales])) < 10000

THEN SUM([@Fake Sales])

ELSE NULL

END

This will result in @Fake Sales when the minimum figure is less than 10,000.  Once you’ve created this calculation, change the default formatting to be currency with no decimal places.

          @Fake Sales in K

IF WINDOW_MIN(SUM([@Fake Sales])) >= 10000 AND

WINDOW_MIN(SUM([@Fake Sales])) <  1000000

THEN SUM([@Fake Sales])

ELSE NULL

END

This will result in @Fake Sales when the window minimum is greater than or equal to 10,000 but less than 1,000,000.  Change the default formatting to be currency with one decimal place and display unit of K. 

          @Fake Sales in M

IF WINDOW_MIN(SUM([@Fake Sales])) >= 1000000

THEN SUM([@Fake Sales])

ELSE NULL

END

This will result in @Fake Sales when the window minimum is greater than or equal to 1,000,000.  Change the default formatting to be currency with one decimal place and display unit of M. 

Now build the view with Region on the Rows and all three of these calculated fields on the Text card.  Click on the Text card and click the “…” next to Text.  Place all three calculated fields on the same line with no spaces between them.  Go back and set the alignment to Middle Right (right aligned on horizontal and center aligned on vertical).  Note: in order to have a cleaner results set for displaying this functionality, I also filtered down to just Technology as well as Consumer and Corporate.

Now your table automatically adjusts its number formatting simply based on the numbers in the view.  Again, adjust the filters to see the number formats change automatically.  

Forums Example

As mentioned previously, this issue comes up quite often on social media and on the Tableau forums (this actually served as my motivation for writing this blog post).  One such tricky situation recently came up on the Tableau forums, which was resolved by my brother Ken. 

The developer was working with a table with a grand total.  He wanted his user “to be able to click on a cell for a specific week and for that cell to change color and be excluded from the Total shown.  Ideally the user could select multiple weeks to be excluded.”  Ken solved this problem using a set action and the same general technique I’ve described above.  Check out the question and answer on the following thread:

https://community.tableau.com/thread/327429

Wrap Up

I hope this new blog post detailing an old trick has been useful.  Although I certainly only covered a portion of potential use cases, I hope that you can utilize this information to apply it toward your individual circumstance.  And as always, if you ever have any questions or comments, please feel free to reach out to me at any time.

Kevin Flerlage, February 3, 2020
Twitter | LinkedIn | Tableau Public