Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Here is a short tutorial that describes how to create a gauge that will either read On- or Off-Target.
Application:
Gauges have a low data-to-area and data-to-ink ratio. Whether this is good or bad is for you do decide. However, in essence, a dashboard gauge needs to be both simple and easy to read at a glance. I used the following gauge to visualize data that rarely, if ever, deviated from the budget, but was nevertheless essential to display.
Steps:
1) Go to New Sheet Object --> Chart -- Gauge
A rule of thumb is to not define a dimension, however I used Month() as my dimension in order to force my gauge to dynamically display whether or not a metric is on or off-target for any month() selected. You can leave the dimension field blank, or use DATE, YEAR(), WEEK(), etc. depending on you need at hand.
2) Within the expressions tab, enter something similar to the following.
=IF(Sum({$<[Metric]={'Metric 1}>} [Sales])>=0, 0.75, .25)
The latter of this expression is essential in order for the gauge to work properly. It will point the ticker in either the red (0.25) or green (0.75) based on a conditional statement that compares a given metric (in this case 'Metric 1'), to another, such as [Sales].
3) Within the presentation tab, format the segments as follows.
Gauge Settings: Min: 0 and Max: 1
Segments Setup: Segment 1: Lower bound 0 and Segment 2: Lower bound 0.5
4) Lastly, within the presentation, add the following expressions within the Text in Chart box located in the bottom right. Click add, and enter the following expressions within the Text field.
=If(Sum({$<[Metric]={'Metric 1'}>} [Sales])>=0,'On Target', 'Off Target')
='Deviation: ' & chr(13)
& Sum({$<[Metric]={'Metric 1}>} [Sales])
Depending on your preferences, edit the font and color as needed, but maintain a transparent background. If the preceding steps are done sequentially, the following will be the end result.
Gauge used to compare Actuals to Plan in order to determine at a glance if we are On- or Off-Target.
Hope this helps.