Background
When using a parameter control to change a measure in a chart, did you ever wonder how to also change the number format accordingly as well? With a traditional parameter control to change a measure, there is no number formatting. Even if you have set them previously to your measures. If you are not familiar with the concept of swapping measures with parameter controls, check out this post to refresh your memory. Also see this short video below demonstrating what I am talking about.
The swapping measures technique is a powerful tool to use for various reasons. One of them being a good trick to save space in your dashboards. Instead of creating multiple versions of the same type of chart with different measures. You can create a parameter to let the user change the measure on one chart.
However, with this parameter control. You will not have the number formatting you have implemented in your measures. Let’s say one measure is a currency and another is in a percentage format. Unlike setting the default number formatting. You will need to implement an alternative way to format your numbers. In this blog, I will go through the steps in order to configure it with an example.
What I would like to do with these measures is add “$”, “%” and “B” for billions as well as adjusting the number itself accordingly. Just like setting up a default number format for a single measure:
As you can see from the video above, there are no currency or percentage icons, even though they were set on the measures beforehand. I will need to configure some more calculated fields in order to achieve this.
The Example
As an example, I will use a dataset that contains military spending information about countries over the years. In my dataset, I have four measures in question:
- Total Spending
- Spending per Capita
- % of Government Spending
- % of GDP
Creating the Calculated Fields
What I want to do is show “Total Spending” and “Spending per Capita” in billions and show the percentage fields with a percent icon. Therefore, aside from my measure swapper calculated field that I created, I also need to create two additional calculated fields for a prefix and a suffix. My initial calculated field with the CASE statement looks like this:
Since I want to show the spendings in billions, I divided the numbers by a billion and round them as well, since my data just shows the raw numbers (ex: 54,000,000,000 and 0.54). I did the same for the percentages by multiplying them by 100 and rounding also.
Next on my list is to create the prefix and suffixes for these new numbers I created. For the currency fields I want to have “$” and “B” and “%” for the percentage fields. Therefore, below are my calculated fields created for this purpose:
Put it all Together in the Label Card
Once the prefix and suffix calculated fields are set, all we need to do is put them together. So, in our sheet, we will need to use all of the calculated fields that we created. If I want to display this information in text form. My calculated fields will all need to go into the Label marks card. And the configuration in the label marks card as so:
Final Look
WIth this configuration, the prefix, the actual measure and the suffix come in a succession. Once this is configured your measure swapping parameter becomes something like this:
Hopefully this blog has been beneficial for you if you were looking to learn how to conditionally change the number formatting of your measures with parameters.
Check out our blogs page to view more blogs on Tableau, Alteryx and Snowflake here.
Work together with one of our consultants and maximise the effects of your data.
Contact us, and we’ll help you right away.