Conditional Formatting on Charts
by Michael Raam , Principal Data Analytics - Pyramid Analytics
Quite often there is a need to present a chart then cannot be found simply in the point-and-click menus within BI Office. However, this does not mean they do not exist, or cannot be created with some creative thinking. In this post, we will review conditional formatting on charts.
Figure 1. Examples of charts displaying conditional formatting .
As a rule, within BI Office formatting settings are defined by series. A series typically refers to a column. So if we need a series of values to have a specific format—color, type, mark, etc.—we would need to arrange the grid and data so all these values are in the same column. We will look at a common scenario that illustrates how to create conditional formatting on a chart in BI Office.
How to Create Conditional Formatting
The example chart in Figure 2 depicts sales figures in months where there were negative sales (red) and positive sales (green). All the negative values are displayed in columns on the left and the positive values are displayed in columns to the right, making it easy to discern the worst and best sales months.
Figure 2. Conditional formatting on a column chart.
So how did we do it? The steps are very simple. We just have to do them in the right order.
Step 1: Within Data Discovery, we start with a value on which to base our chart, in this case “Geo Sale,” which is located in the Selection Panel under Elements.
Step 1. Select “Geo Sale” from Selection Panel.
Step 2: We then split the base value into two new values: “Positive geo sales” and “Negative geo sales.” The split can be accomplished in many ways, typically with an “if” statement using the Advanced Calculation Designer wizard, located under the Analytics tab on the ribbon.
Step 2. Split base value into separate values.
Step 3: Once the “Positive geo sales” and “Negative geo sales” values are created, we will select them from within the Elements pane, and then select the Stacked Bar chart, which can be found by clicking the Type button located on the Chart tab on the ribbon. Notice the default color and formatting of the charts are blue. This is because we’re using a theme that applies shades of blue to our charts.
Step 3. Select Stacked Bar chart.
Step 4: Now we will apply the sort on the base value we used, even though it is not visible in the final result. Under the Query tab on the main ribbon, select the Sort button. Confirm the sort is Ascending, and then click OK.
Step 4. Sort values in ascending order.
Step 5: The last step is to apply to colors to the “Positive geo sales” and “Negative geo sales” values by right-clicking on the chart legend, selecting Series Options, selecting Edit Series, and then selecting our desired color. Here we show the steps required to configure the “Positive geo sales” formatting; the “Negative geo sales” is formatted using the same steps.
Step 5. Apply new color scheme.
Once we have finished the settings we have the final result of the chart displaying the new color formatting based on values in ascending order.
Figure 3. Our desired chart with conditional formatting.