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
|Category||Sub Category||MTD Actual||MTD Budget||Delta||Explanation|
|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|
|Revenue||Gross Rent||283,088||279,200||3,888||IF Actual >= Budget, BLACK, RED|
|Vacancy||3,430||4,920||1,490||IF Actual < Budget, BLACK, RED|
|Promotion||1,000||1,500||500||IF Actual < Budget, BLACK, RED|
|Parking||20,000||15,000||5,000||IF Actual >= Budget, BLACK, RED|
|Other Income||50,000||60,000||10,000||IF Actual >= Budget, BLACK, RED|
|Expense||Salaries||100,000||150,000||50,000||IF Actual < Budget, BLACK, RED|
|Advertising||200||100||100||IF Actual < Budget, BLACK, RED|
|Utilities||12,000||10,000||2,000||IF Actual < Budget, BLACK, RED|
|Taxes||20,000||27,000||7,000||IF Actual < Budget, BLACK, RED|