Highlighting conditionally formatted data with color

Use color in your table or crosstab visualizations to see the distribution of your data and highlight exceptional data points. For example, you might want to highlight low sales numbers in red, or use green to highlight sales numbers over a certain threshold.

Before you begin

Depending on the data that you are using and the outcome that you want, you can use several different ways to define thresholds for conditionally highlighting your data in tables and crosstabs.

When you choose a measure to apply conditional formatting to, you can base the conditionality on the measure itself or a related measure of your choosing. Then, you can decide whether you want the evaluation to be based on a numeric value comparison or a percentage comparison. If you choose the Numeric scale, you get a simple comparison of the number value for each row against the rules you define for the comparison. For example, greater than 100 highlights as green and lower than 50 highlights as red. If you choose the Percentage scale, then the measure you selected for conditional formatting is divided by the number you select in the Color By setting for your conditional rules. If the Color By measure matches the measure you are conditionally formatting, the result is always 100% because you are dividing the number by itself.

The following scenarios illustrate common use cases for defining conditional formatting rules. To try the procedures yourself, you can use one of the data samples such as the GO data warehouse (query) sample.

Comparing a measure to static values

In this scenario, you have a value in your data source and you want to classify your result against hard targets. For example, you want to evaluate your revenue result with an arbitrary target of $150 million. If you meet or exceed $150 million, color the cells green. Likewise, if you achieve between $20 million and $150 million, then color the cells yellow. If your revenue is less than $20 million, then color the cells red.

Procedure

  1. Select a crosstab visualization and click the Properties tab Properties icon.
  2. In the Conditional color section of the Visualization tab, click the name of data under Available data to open the Conditional data: Name of data pane.
  3. From the Scale list, select Numeric.
    The default setting is a comparison of the measure to itself with a percentage scale.
  4. Complete the following steps to create a rule:
    1. Under Rules, select Add rule.
    2. Under If value is, select Greater than or equal to.
    3. Type 150,000,000.
    4. Under Rule style, set the text color, fill color, and indicator to match your preferences.
      The default rule style is to color the text green with an Arrow up icon indicator.
    comparing measure to static values properties
  5. Create a rule for the yellow values. Use the Less than condition and the AND option to add a Greater than or equal to condition to the rule. Select an appropriate Indicator, such as the right arrow icon.
  6. Create a rule for the red values. Use the Less than condition and complete the rest of the rule. Select an appropriate Indicator, such as the Arrow down icon.

    comparing measure to static values properties showing green yellow and red

Evaluating a percentage against a static calculation

In this scenario, you have a percentage result, Gross Margin, in your data source that you’re looking to conditionally color. You can use a numeric evaluation as you did in the previous scenario and create rules for the underlying source values such as 0.37 or 0.62. Or, you can create a dummy calculation with a numeric expression of 1 that can be used to divide the underlying Gross Margin values to get the percentage variance.

Procedure

  1. Click Sources sources icon.
  2. Click the More icon More icon on the Selected sources panel, and then click Create calculation calculate icon.
  3. In the Create calculation dialog box, type a name for your calculation such as DummyCalculation.
  4. In the Expression field, type 1.
  5. Click OK.
  6. Select the crosstab visualization on your dashboard and click the Properties tab Properties icon.
  7. In the Conditional color section of the Visualization tab, click DummyCalculation under Available data to open the Conditional data: DummyCalculation pane.
  8. From the Color By list, select DummyCalculation.
  9. From the Scale list, leave the default as Percentage.
  10. Click Add rule.
  11. Under If value is, select Greater than or equal to, and then type 60% for the value.
    Keep the default Rule style formatting.
  12. Click Add rule.
  13. Under If value is, select Less than or equal to, and then type 40% for the value.
  14. Under Rule style, change the Text color to red and change the Indicator to a Arrow down icon.

    Evaluating a percentage against a static calculation showing red and green values

    This procedure works because the underlying source value is divided by the value 1 in the DummyCalculation expression. For example, 0.37 divided by 1 returned as a percentage is 37%.

Coloring a measure based on the variance between two measures

In the previous scenario, you were conditionally highlighting the percentage value Gross Margin in your source data. The percentage calculation might not be included in your underlying model. You can automatically calculate the percentage variance between two measures in the conditional color settings. In this scenario, color your Revenue measure based on the percentage variance of Sales Target.

Procedure

  1. Select a crosstab visualization and click the Properties tab Properties icon.
  2. In the Conditional color section of the Visualization tab, click Sales target to open the Conditional color: Sales target pane.
  3. From the Color By list, select Sales target.
    The default setting is a comparison of the measure to itself with a percentage scale.
  4. Complete the following steps to create rules:
    1. Under Rules, select Add rule.
    2. Under If value is, select Greater than.
    3. Type 110%.
      Keep the default Rule style formatting.
    4. Click Add rule.
    5. Under If value is, select Less than or equal to, and then type 110% for the value.
    6. Under Rule style, change the Text color to red and change the Indicator to an Arrow down icon.
    Coloring a measure based on the variance between two measures

    In this scenario, if you divided the first row Revenue value by the first row Sales Target value, the result is 1.12482. This result is a percentage return of 112% and is why Revenue in the first row is conditionally formatted as green.

Coloring a measure based on a benchmark calculation

In this scenario, highlight Revenue based on how it compares to Sales Target. If Revenue is greater, it is green. If Revenue is lower, it is red. To achieve this result, create a calculation that compares the two values and assigns values of 1 or 0.

Procedure

  1. Click Sources sources icon.
  2. Click the More icon More icon on the Selected sources panel, and then click Create calculation calculate icon.
  3. In the Create calculation dialog box, type a name for your calculation such as Revenue Benchmark.
  4. In the Expression field, type the following text: 
    If 
([Sales].[Sales].[Revenue] > [Sales Target].[Sales Target].[Sales Target]) 
then 
(1)
else 
(0)
  5. Click OK.
  6. Select the crosstab visualization on your dashboard and click the Properties tab Properties icon.
  7. In the Conditional color section of the visualization tab, click Revenue under Available data to open the Conditional color: Revenue pane.
  8. From the Color By list, select Revenue Benchmark.
  9. From the Scale list, select Numeric.
  10. Click Add rule.
  11. Under If value is, select Equal to, and then type 1 for the value.
  12. Under Rule style, change the Indicator to a Checkmark icon.
  13. Click Add rule.
  14. Under If value is, select Equal to, and then type 0 for the value.
  15. Under Rule style, change the Text color to red and change the Indicator to an X symbol icon.

    Coloring a measure based on a benchmark calculation