1 Reply Latest reply on May 16, 2018 5:58 PM by Esther Aller

# Color Black or Red for EACH Calculated Measure depending if it's positive or negative number

Hello Tableau Experts,

A little explanation here.

For Revenue, if Actual >= Budget, then it's a good thing. However, 'Vacancy' and 'Promotion' is the opposite of Revenue (although it is categorized under 'Revenue'), thus, if Actual< Budget, then it's good. Also since people like to see things in positive number, under 'Revenue' category, all should be shown as positive numbers, except 'Vacancy' and 'Promotion', where it shows as negative number.

For Expense, if Actual < Budget, then it's a good thing.

Last but not least NOI Margin % is the same as 'Revenue'  and  'Economic Vacancy %' is the same as Expense.

I've created multiple Calculated Measures, but what I'll be showing are [MTD Actual], [MTD Budget] and [Delta].

And since for each Calculated Measures, I have to show numbers and percentages depending on the categories, I use a custom format trick (#,###;##0.0%) where I have to 'convert' all numbers to positive, and all that's percentages to negative.

Notice that in [MTD Actual] Vacancy and Promotion, the number is literally shown as a positive number. However, due to 'Conditional Format', I have color it red to show that it's a "negative" number

I was able to show the correct coloring when it's only 1 measure, i.e. [MTD Actual] or [MTD Budget].

However, when I introduce more than 1 measure, the coloring is applied across the measures.

I am thus, wondering if there's a way to color only for each measure?

A good example is 'Vacancy'. Notice that [MTD Actual] < [MTD Budget], thus, I'd like to show the Delta as a BLACK color variance. But since 'Vacancy' is a negative number, I'd like to have it shown as Red color for [MTD Actual] and [MTD Budget] calculated measures.

Table below is the desired result

CategorySub CategoryMTD ActualMTD BudgetDeltaExplanation
NOI Margin %55.8%54.1%1.7%IF Actual >= Budget, BLACK, RED
Economic Vacancy %1.2%1.8%0.6%IF Actual < Budget, BLACK, RED
RevenueGross Rent283,088279,2003,888IF Actual >= Budget, BLACK, RED
Vacancy3,4304,9201,490IF Actual < Budget, BLACK, RED
Promotion1,0001,500500IF Actual < Budget, BLACK, RED
Parking20,00015,0005,000IF Actual >= Budget, BLACK, RED
Other Income50,00060,00010,000IF Actual >= Budget, BLACK, RED
ExpenseSalaries100,000150,00050,000IF Actual < Budget, BLACK, RED
Advertising200100100IF Actual < Budget, BLACK, RED
Utilities12,00010,0002,000IF Actual < Budget, BLACK, RED
Taxes20,00027,0007,000IF Actual < Budget, BLACK, RED
• ###### 1. Re: Color Black or Red for EACH Calculated Measure depending if it's positive or negative number

Hey David,

Since [Measure Names] is a generated field that cannot currently be used in calculations, what we need is to have a separate Marks card for every column in the view. This would allow us to put a different color calculation on each column.

We can get those extra Marks cards using dummy calculated measures. This method is option 4 in Conditionally Color the Text in a Crosstab | Tableau Software

Actually, you might want to look at option 1 too. If you can do a 2-step diverging red-black color for each measure based on its own values, then that is way simpler to set up and maintain.

Hope that works for you

1 of 1 people found this helpful