Formatting for Variables that have different ranges

Shevy · 2025-04-11T19:45:44+00:00

There was an error rendering this rich post.

I have a data set with a variable where the user can select a metric to view. Some choices are dollars / values and others are percentages. Any way to get the format to change depending on the metric chosen? As a side question to this, could the conditional formatting of the table be different based on the metric?

Find more posts tagged with

Formatting

Formulas

Variables

Accepted answers

ggenovese

One option is to create a beast mode with a case statement that applies a dynamic format based on the variable. There is a MySQL Function called FORMAT() that you can use in a beast mode even though it's not a documented beast mode function and it will give you a validation error.

Something like this:

CASE 
WHEN `My Variable` = '$ Sold' THEN CONCAT('$ ', FORMAT(SUM(`Sold`),2)))
WHEN `My Variable` = '% Change' THEN CONCAT(FORMAT(((SUM(`Current Value`) - SUM(`Previous Value`)) / SUM(`Previous Value`))*100,2),'%')
ELSE FORMAT(SUM(`Units`),0)
END

All comments

ggenovese

Something like this:

CASE 
WHEN `My Variable` = '$ Sold' THEN CONCAT('$ ', FORMAT(SUM(`Sold`),2)))
WHEN `My Variable` = '% Change' THEN CONCAT(FORMAT(((SUM(`Current Value`) - SUM(`Previous Value`)) / SUM(`Previous Value`))*100,2),'%')
ELSE FORMAT(SUM(`Units`),0)
END

Manasi_Panov

Hi @Shevy,

For charts and graphs, use a separate Beast Mode with the Tooltip function to dynamically switch between %, $, or units.

Shevy

Don't love this solution but it can work. Thank you