Apply Conditional Formatting Based on Another Metric

  • 19 April 2024
  • 0 replies
  • 136 views

Userlevel 4
Badge +3

Conditional formatting based on another Metric allows you to apply conditional formatting on a Metric or Dimension Item based on the outcome of a Metric within the same table.  The flexibility to format based on more specific conditions gives you greater depth when analyzing your data.

 

Completely new to conditional formatting? Not a problem. We recommend that you first take a look at Apply Conditional Formatting to Highlight Data Changes. This article introduces you to conditional formatting. It describes how to apply conditional formatting, explains conditional formatting rules, and also helps you understand formatting options.  

 

Considerations 

Before you start applying conditional formatting based on the outcome of another Metric, here’s some useful information to keep in mind:
 

  • Application compatibility: You can apply conditional formatting based on another Metric to a Metric Header. In this instance, Pigment compares all cells belonging to the other Metric to those conditions.
    You can also apply conditional formatting based on another Metric to a Dimension Header. When you chose to do this, the following happens: 
    • if the Dimension is on the same axis as the Metric, all cells belonging to the Dimension Item and the Metric used for the conditional formatting coordinates will be colored if the condition is matched.
    • if the Dimension isn’t on the same axis as the Metric, coloring is applied to all cells on the Dimension Item axis if the cells at the intersection with the other Metric matches conditional formatting’s condition.
       
  • Metric Compatibility. Both the Metric in which you set the conditions and the Metric where the formatting is applied must be in the same Table. The Metric in which you set the conditions can be a hidden Metric.

The Metric in which you set the conditions and the Metric where the formatting is applied doesn’t necessarily need to have the same Dimensions. However, the conditional formatting rule when using a Dimension, which is not part of the structure of one of the two Metrics, needs to be applied at the total level. Also, the total for both Metrics needs to be displayed in the grid.

  • Metric Conditions. The type of conditions available in your conditional formatting rule depend on the type of the Metric chosen. For example, Metrics that have a Text type have different condition options compared to Metrics that are numeric.
     
  • Metric Updates. Let’s say you modify the Metric used for conditions. If you change the dimensionality or the data type, or if you remove it from the Table, then the conditional formatting rule may break. If for any reason your conditional formatting rule doesn’t work, it’s worth checking any modifications that were recently applied.
     
  • Cell-Level Matching. If there are Dimension items underneath the Metric where you are applying the conditional formatting, the condition match is done for each individual cell. Conditions are evaluated for each corresponding cell value based on the conditions set.

    For example, let’s say you want to format the Revenue according to the growth % and you have one row per Country. The rule first checks the conditions on all countries for the growth %, and then it applies the formatting to the corresponding country on the Revenue Metric.
     

Apply a Rule to a Metric

 

  1. Right click on the Metric header you want to highlight
  2. Select Conditional Formatting
  3. Click Another Metric to select a source Metric. 
  4. Select the required Metric you want for this rule. 
  5. Confirm the Condition and if applicable, the Value.
    The values available in Condition and Value depend on the source Metric. 
     
    Apply a Rule to a Metric
  6. If applicable, indicate to which cells this rule applies.
  7. Select the different formatting options you wish to apply.
  8. Click Done. 

 

The Conditional Formatting pane displays your new conditional formatting rule. You can manage, edit, or delete these rules as needed. For more information, see Apply Conditional Formatting to Highlight Data Changes.

 

Apply a Rule to a Dimension Item

 

  1. Right click on the Dimension Item header you want to highlight.
  2. Select Conditional Formatting
  3. Click Another Metric to select a source Metric. 
  4. Select the required Metric you want for this rule. 
  5. Confirm the Condition and if applicable, the Value.
    The values available in Condition and Value depend on the source Metric. 
     
    Apply a Rule to a Dimension Item

     
  6. If applicable, indicate to which cells this rule applies.
  7. Select the different formatting options you wish to apply.
  8. Click Done. 

0 replies

Be the first to reply!

Reply