Alerting max/min in Column chart (SAC)

[avsalynin]

INTRODUCTION

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.

STEP-BY-STEP

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

Type: Aggregation;

Operation: MAX;

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;

Formula:

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:

Result:

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)

Hex: #c9024a

Result:

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])

Final result:

Of course, this logic will work dynamically with story filters, input controls (screenshots below) and linked analysis.

SUMMARY

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.

[paddyw]

Thank you for the example. May I ask why the gross margin will not show up for Kiren Raj? And how is the dimension constant work in your example? Thank you.

[Daniel_Davis]

Great blog, but clearly i'm doing something wrong - I follow steps 1-5 and just have the 3 measures in the chart but I end up with Kiran Raj having 2 bars, one green and one blue both at 47.47 - that's actually the expected behaviour I think. How do you remove the Blue bar for Kiran Raj for the Gross Margin measure and the second bar for James Frank as there is nothing preventing it appearing?  Did I miss a step or something?

[Daniel_Davis]

I think we have the same question - if you look at my comment below I have 2 bars for Kiren Raj ( as I think you do ) and the example only has one?

Is this the same issue?

[paddyw]

Yes, you are correct, exactly same question, I have two bars will show up.

[former_member30165]

Good one

[avsalynin]

danieldavis

yes, I'm sorry, I missed one important point!

Gross margin, which in the final chart is not a standard one from the model, but also a calculated one.

Here is the formula:

IF([#MAX Gross]!=[#MAX Gross Constant] AND[#MIN Gross]!=[#MIN Gross Constant] ;["BestRunJuice_SampleModel":Gross_Margin])

With the help of this formula, we remove duplication and voids.

Added to the article now.
Thank you

[avsalynin]

dir_1118

answered separately to danieldavis

Updated the article with missing information. Thanks!

[Daniel_Davis]

Ah that cracked it - but I think my seperator in the formula editor is a COMMA ',' not a SEMICOLON ';' but with this

IF([#MAX Gross] != [#MAX Gross Constant] AND [#MIN Gross] != [#MIN Gross Constant] ,["BestRunJuice_SampleModel":Gross_Margin])

it works - many thanks...

[avsalynin]

screen:

[Daniel_Davis]

I'm not sure why my default separator is a COMMA not a SEMI COLON but with the correct formula it works perfectly for me... nice blog !!!