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

    David Luhut

      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