Highlighting change for a subset of measures

This post was inspired by a question on the Tableau forums. Basically the goal was to display a combination of measures in a single table. Some measures were percentages and others whole numbers. Added to this was a desire to highlight change (up or down) for percentages but not for the whole number values. The highlight table seemed to be the best solution, but by default it applies the highlight to all values. So, it took some tweaking to get to trick Tableau into not highlighting the changes in whole numbers.

The solution involved creating one variable that contained the names of the measures and a second variable that contained the values. Next calculated fields were used to create different measures for numbers and percentages. Those calculated fields were then used to create a new single measure that had whole numbers as positive values and percentages as negative values. (The trick of custom formatting positive and negative numbers to display whole numbers and percentages is covered in a previous post).

When this measure was added to the highlight table, Tableau wanted to use it to set the color scheme which applied to all values. So, a new calculated field was created to assign a new value based on the direction of change. Here is the formula that I used:

  IF ISNULL(SUM([Value %])) THEN 0 ELSE
     IF (ZN(SUM([Value %])) - LOOKUP(ZN(SUM([Value %])), -1)) < 0 THEN 10
     ELSE 20 
     END
  END

Notice that the first "IF...THEN" statement says that if the value is a whole # (based on the calculated field created earlier) then it is coded as a 10. For the % values, a downward change is coded as a 0 and upward change is coded as 20. This new calculated field is then put on the color tab. By changing to a diverging color scheme (e.g. red, white, green which is a built in option), red can be applied to downward changes and green is applied to upward changes. The trick here is that we are actually highlighting the whole numbers in white (the background color of the sheet) so it appears that nothing is happening to those values. The final result is below: