Last Modified Date: 19 Feb 2018
Make a calculated field for each possibility in the parameter as follows. In this example, we have a Parameter with two options Sales as Currency and Discount as Percentage:
- Calculated field 1
IF [Parameter] = 'Sales' THEN [Sales] ELSE NULL END
- Calculated field 2
IF [Parameter] = 'Discount' THEN [Discount] ELSE NULL END
Set the Default Number Format for these new fields through the measure window and add both to the Label text shelf. Only the one selected in the parameter will show.
Formatting tip: By default, Tableau Desktop will put an enter break between every measure on Text. To remove this extra white space, click on Text and then on the "..." button to open the Edit Label dialog, and remove all spaces or breaks between measures.
Option 2If you want one measure to display as a percentage and a second measure to display as currency, use the following workaround:
- Use a calculated field to convert the measures to strings, and then format the strings within the calculated field. The following formula provides a template that can be used for the calculated field:
CASE [<Measure Swapping Parameter>] WHEN '<Measure A>' THEN LEFT(STR(AVG([<Measure A>])*100), 3) + "%" WHEN '<Measure B>' then "$" + (if SUM([<Measure B>])>99999 THEN LEFT(STR(SUM([<Measure B>])), 3) + "," + MID(STR(SUM([<Measure B>])), 4, 3) END) END
- In the view, replace the parameter with the newly created calculated field.
For more information about strings, see String Functions in Tableau Help.
Option 3Create two worksheets and swap between them on a dashboard
- Create one worksheet that shows SUM( [Sales] )
- Right-click the worksheet tab and select Duplicate Sheet.
- On the new worksheet, replace SUM( [Sales] ) with SUM( [Profit] ) / SUM( [Sales] )
- Follow the directions in Create a View (Sheet) Selector for Your Dashboard to swap between the worksheets on a dashboard.
CauseWhen measures are used in a parameter, the calculated field that generates the measure values for the parameter is considered a single field. As a result, it is not possible to format the individual measure values used in the parameter.
Additional InformationMore informations can be found in the following third party links:
- InterWorks Blog: Extreme Viz Makeover with Tableau
- Tableau Tip Tuesday: How to Use One Parameter With Unlimited Number Formats
Thank you for providing your feedback on the effectiveness of the article.
Open new Case
Training and Tutorials