Resolution
Option 1
Use this option when you have only one measure in the view at a time.
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:
IF [<Measure Swapping Parameter>] = 'Sales' THEN [Sales] ELSE NULL END
IF [<Measure Swapping 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 2
Create one calculation that converts the numeric data to text, string, or data, and then manually add the formatting to the calculation.
In this example, we have a parameter with two options Sales as currency with a comma for the thousands place, and Discount as percentage with one decimal place:
- 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 'Discount' THEN LEFT(STR((CEILING(AVG([Discount])*10000))/100),LEN(STR(FLOOR(AVG([Discount])*100)))+2) + "%"
WHEN 'Sales' then "$" +
IF SUM([Sales])>999
THEN LEFT(STR(FLOOR(SUM([Sales]))),LEN(STR(FLOOR(SUM([Sales]))))-3) + "," + RIGHT(STR(FLOOR(SUM([Sales]))), 3)
ELSE LEFT(STR(SUM([Sales])), 3)
END
END
- In the view, replace the parameter with the newly created calculated field.
(Optional) Because the String function will reorder the fields in the view alphabetically, right-click the calculated field with the Case Statement and manually sort the Months field into the desired order.
For more information about strings, see String Functions in Tableau Help.
Option 3
Create two worksheets and swap between them on a dashboard. This is generally the best option when swapping dates.
- 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.