Advance with Assist shares quick solutions to common challenges encountered by the InterWorks Assist on-demand team.

Question: I’ve used a case statement to allow my user to switch between measures, but I can’t format my percentage correctly. It always shows as a decimal. Is there a way around this?

The short answer is yes but with the requirement that you can’t have negative numbers. All measures need to return positive values in order for this to work. Let me explain.

Here’s a simple report showing what this user was after. I’ll use Superstore data to illustrate:

Single Value Display, Multiple Format Options

The goal is to only show one measure at a time but have the correct formats. To give the user the ability to select the measure they want to show, here is the process we would follow.

1. Create a parameter:

2. Create a calculated field with CASE based on the selection criteria:

Now, using this calculation and parameter in the Tableau report, you are able to select the metric you wish to show. This is also the point at which our formatting issue appears. Even though our Discount measure has a default format of percentage, only one format can be used on the calculated field we’ve created:

Editing the Calculation for Metric Selection

Here’s where a subtle change to the calculation and a custom format allow you to gain the desired results.

3. Change to the calculation, adding a negative sign ( – ) to the Discount: 

4. Now, change the Default format on the Metric Selection calculation you’ve made to be #,##0; 0.0% 

You now have one calculated field with multiple formats based on the values presented.

Discount

Sales

Note: Remember that only positive values work for this format.