Alerting max/min in Column chart (SAC)
We know that the topic of alerting something is very important for analytics and for the speed of perception of the desired values.
Of course, in SAС everything is in order with this: there is a convenient Conditional formatting, which is available for use both in KPI tiles and in charts and tables.
But at the moment, in the settings of the conditional formatting, there is no way to choose the calculation logic (AVG, MAX, MIN, sum, etc.)
There are such scenarios when you need to present not just a bar chart (in this article we will consider it using an example):
and highlight the columns of the maximum and minimum values in it:
Below in the section I will describe in detail the steps on how to do this.
For this example, I took one of the standard SAC models – BestRunJuice_SampleModel.
And built an ordinary Bar Chart. For example, I took the Gross Margin measure and Sales Manager dimension.
1. CREATE AGGREGATION MAX MEASURE
Aggregation Dimensions: Sales Manager
2. CREATE RESTRICTED CONSTANT MEASURE
Type: Restricted Measure;
Measure: MAX Gross (from prev. step);
Dimensions: Category – Actual
Constant Dimensions: Category, Sales Manager
Needed to fix the maximum values throughout the chart:
3. CREATE CALCULATED MEASURE (WITH IF)
Type: Calculated Measure;
IF([#MAX Gross]=[#MAX Gross Constant] ;[#MAX Gross] ;NULL)
4. COLORIZE MAX(Gross Margin) MEASURE
Set the color (Hex: #37962d) for this measure in the color section:
5. MIN MEASURES
Similarly steps (1-3) for a scenario with a minimum.
Operation: MIN; Measure: MIN Gross (for min); Formula:
IF([#MIN Gross]=[#MIN Gross Constant] ;[#MIN Gross] ;NULL)
6. GROSS MARGIN IF
Now we need to remove duplication and voids. To do this, instead of the standard Gross Margin, add a calculated measure with the formula:
IF([#MAX Gross]!=[#MAX Gross Constant] AND[#MIN Gross]!=[#MIN Gross Constant] ;["BestRunJuice_SampleModel":Gross_Margin])
Of course, this logic will work dynamically with story filters, input controls (screenshots below) and linked analysis.
As a result, in this scenario, we had to create a total of 7 measures: 3 final and 4 dependent.
If there are any simpler ways to solve this case (may be in one measure 🙂 ) with highlighting max / min in the charts, write in the comments.