Bubbles alerting

The idea of this publication is to illustrate a particular point of alerting or conditional formatting.

We set conditional formatting for several reasons but the most used is the one concerning measures with thresholds: green, yellow, red.

This is pretty easy to create and it reflects the figures for a given result at a given detail level.

The idea of this document is to show how we can propagate an alert in all levels of a hierarchy (natural or not) in a report.

For example, I am the sales director for a given region and I want to know if all sales perform well.

My region is organized in countries, departments, …, and sales. So I need to know at a given level in the hierarchy if the results are good or not.

In the document example the data are organized as following:

• Country
• Resort
• Service Line
• Service

My objective is to know at each level of the hierarchy if the services are performing well. Results are analyzed through the Revenue measure.

To do so I will create measures variables for each level, alert variables for each level, and I will manage the different thresholds though 2 input controls linked to one variable each.

Here is a first screenshot of my report:

Revenue variables

I need to create one Revenue variable per level / dimension of my report.

And the most important one I need to set the minimum value for a given level to be able to create the thresholds and then the formatting rules?

• Min Revenue per Service Line
  • =Min ( Min([Revenue] ForEach ([Country]; [Resort]; [Service Line]; [Service])) ForAll ([Service]) )
• Min Revenue per Service
  • =Min([Revenue] ForEach ([Country]; [Resort]; [Service Line]; [Service]))
• Min Revenue per Resort
  • =Min ( Min([Revenue] ForEach ([Country]; [Resort]; [Service Line]; [Service])) ForAll ( [Service Line]; [Service]) )
• Min Revenue per Country
  • =Min ( Min([Revenue] ForEach ([Country]; [Resort]; [Service Line]; [Service])) ForAll ( [Resort]; [Service Line]; [Service]) )

Thresholds variables

I will create now 2 variables to be used by the input controls and the alert variables to define the thresholds.

We need 2 measures because we will use one input control slider per variable. We cannot a use a double slider because it’s not a real measure that will be filtered by an input controls but a measure value modified through an input control.

• Bubble Min
  • =9000
• Bubble Max
  • =50000

Of course the values set for the Min and Max are depending on the data used in the report.

Alert variables

I need to create one alert variable per level / dimension of my report.

Each alert variable is based on its respective calculated measure

• Revenue alert Service Line
  • =If ([Min Revenue per Service Line]> [Bubble Max]) Then 2 ElseIf ([Min Revenue per Service Line] > [Bubble Min]) Then 1 Else 0
• Revenue alert Service
  • =If ([Min Revenue per Service]> [Bubble Max]) Then 2 ElseIf ([Min Revenue per Service] > [Bubble Min]) Then 1 Else 0
• Revenue alert Resort
  • =If ([Min Revenue per Resort]> [Bubble Max]) Then 2 ElseIf ([Min Revenue per Resort] > [Bubble Min]) Then 1 Else 0
• Revenue alert Country
  • =If ([Min Revenue per Country]> [Bubble Max]) Then 2 ElseIf ([Min Revenue per Country] > [Bubble Min]) Then 1 Else 0

Conditional formatting rules

I will add for each dimension in my report a conditional rule based on the previously created alert variables. One formatting rule is based on its respective alert variable: Service Line conditional rules is based on [Revenue alert Service Line], etc.

• If the alert variable is equal to 2, the foreground color is green.
• If the alert variable is equal to 1, the foreground color is yellow.
• If the alert variable is equal to 0, the foreground color is red.

Input controls

Now I need to create the 2 input controls based on [Bubble Min] and [Bubble Max] variables.

We can set a min and a max for Bubble Min that does not overlap the Min and the Max for Bubble Max.Each input control is based on a simple slide

Report results

I can now play with the input controls to change the thresholds and see the bubbles alerting propagated along the different levels / dimensions of the report.

In conclusion, it’s an easy way to bring information to the top and to know if a given person, product, service, etc., is performing well.

You can download the Web Intelligence report attached to that publication.

Didier MAZOUE