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
Bluesky
