In one of the data visualizations I was working on I encountered a situation where I wanted to be able to switch between a display of the headcount and the percentage of people in categories. In researching the issue, I discovered that what I wanted to achieve could be done with the use of a parameter. The trick to get this to work was the use of custom formatting a measure to display positive and negative values differently, and then using a parameter to set the value being displayed.
Continue on to see the steps and an example of the feature in use…
To start, create a parameter that allows the user to switch between Headcount or Percent. In my case, I called the parameter “Display Values”. Next, create a calculated value with the following formula:
IF [Display Values] = "Headcount" THEN sum([Headcount]) ELSE -1* (SUM([Headcount])/TOTAL(SUM([Headcount]))) END
You can now use the calculated value you created as the measure to be display.
The final step is to add the custom formatting for the measure. Rigth click on the measure name and select “custom” format. The first part of the custom format refers to how to display positive numbers, and the second part (after the ‘;’) refers to negative values. Since Headcount will always be positive and Percent of Students is always negative we can format the two values different. Use the following for the custom format:
This results in commas and no decimal for positive numbers. For negative numbers one decimal point is included and a % sign is added to the end. Using a 0 instead of a # sign tells Tableau to include zeros, so for decimal places this makes sure that we have both a leading zero and a value after the decimal value.
Here’s an example of this in action based on some hypothetical enrollment data…