How to make your Gauge chart dynamic

I don’t know how many customers and consultants that have asked: “can I get a dynamic target in my gauge chart?”. I even got the question asked today. My reply is always: “Sorry, this is not possible with standard Salesforce reports and dashboards”. But today I also thought: “I wonder if Wave could support this requirement?”, the answer is “Yes!”.

In my example, I make the assumption that we have an actual number and a target number. I am using activities, but you can, of course, apply this example to opportunity amount or forecast.

Gauge Chart

Start by creating a lens with a gauge chart.

Then clip it to the dashboard.

In the UI you can change some of the widget properties of the gauge including the breakpoint color, labels, and values. But before we get to that we first need to create a new step that calculates the breakpoints we want to use in order to make the gauge dynamic.

Target Breakpoints

In my dataset I not only have my actual activity number, I also have my target number. I will use this target as my “max” breakpoint, but I also want to calculate what I will be using for my “medium” and “high” breakpoint. For this, I will use a compare table. I am keeping my “min” as is, since I want my gauge to start at “0”.

Click on “Create Step” in the step overview.

Choose the “sum of target” and change the format to a “compare table”.

Next, we need to add a column for our “medium” breakpoint. I’ve chosen that my “medium” breakpoint is 1/3 of my target breakpoint, so I want to convert the column into a formula and use “A/3”.

Similarly, I want to create a column more to give me a breakpoint for “high”. I’ve chosen “high” is 1/2 of my target. So I end up with the following.

Now it just needs to be added to the dashboard. To make every breakpoint clear I have added each of them as a number to the dashboard.

Add the breakpoints to the gauge

Now we have what we need in order to change the values in the gauge based on what the compare table calculates. This is done in the dashboard JSON (Command+E or Control+E).

First, we need to remember the name and column names of the compare table we created.

 "DynamicBreakpoints_1": {
 "type": "aggregateflex",
 "visualizationParameters": {
 "options": {},
 "type": "table",
 "parameters": {
 "customBulkActions": [],
 "mode": "variable",
 "minColumnWidth": 40,
 "maxColumnWidth": 300,
 "totals": true
 }
 },
 "datasets": [
 {
 "id": "0Fb58000000CeaZCAS",
 "name": "TargetActual1",
 "label": "TargetActual",
 "url": "/services/data/v40.0/wave/datasets/0Fb58000000CeaZCAS"
 }
 ],
 "isFacet": true,
 "useGlobal": true,
 "isGlobal": false,
 "query": {
 "columns": [
 {
 "query": {
 "measures": [
 [
 "sum",
 "Activities_Target"
 ]
 ]
 },
 "header": "Max"
 },
 {
 "query": {
 "measures": [
 [
 "sum",
 "Activities_Target"
 ]
 ],
 "formula": "A/3"
 },
 "header": "Medium"
 },
 {
 "query": {
 "measures": [
 [
 "sum",
 "Activities_Target"
 ]
 ],
 "formula": "A/2"
 },
 "header": "High"
 }
 ],
 "measures": [
 [
 "sum",
 "Activities_Target",
 "A"
 ],
 [
 "sum",
 "Activities_Target",
 "B"
 ],
 [
 "sum",
 "Activities_Target",
 "C"
 ]
 ]
 },
 "label": "DynamicBreakpoints"
 },

Next, we find the gauge widget.

"chart_1": {
 "type": "chart",
 "parameters": {
 "visualizationType": "gauge",
 "title": {
 "label": "",
 "subtitleLabel": "",
 "align": "center"
 },
 "theme": "wave",
 "showValues": true,
 "axisMode": "multi",
 "autoFitMode": "keepLabels",
 "binValues": false,
 "bins": {
 "breakpoints": {
 "low": 0,
 "high": 100
 },
 "bands": {
 "low": {
 "label": "",
 "color": "#B22222"
 },
 "medium": {
 "label": "",
 "color": "#ffa500"
 },
 "high": {
 "label": "",
 "color": "#008000"
 }
 }
 },
 "dimensionAxis": {
 "showAxis": true,
 "showTitle": true,
 "title": "",
 "customSize": "auto",
 "icons": {
 "useIcons": false,
 "iconProps": {
 "column": "",
 "fit": "cover",
 "type": "round"
 }
 }
 },
 "measureAxis1": {
 "sqrtScale": false,
 "showAxis": true,
 "customDomain": {
 "showDomain": false,
 "low": null,
 "high": null
 },
 "showTitle": true,
 "title": "",
 "referenceLine": null
 },
 "measureAxis2": {
 "sqrtScale": false,
 "showAxis": true,
 "customDomain": {
 "showDomain": false,
 "low": null,
 "high": null
 },
 "showTitle": true,
 "title": "",
 "referenceLine": null
 },
 "legend": {
 "show": true,
 "showHeader": true,
 "inside": false,
 "position": "right-top",
 "customSize": "auto"
 },
 "trellis": {
 "enable": false,
 "showGridLines": true,
 "flipLabels": false,
 "type": "x",
 "chartsPerLine": 4
 },
 "exploreLink": true,
 "showValue": true,
 "showPercentage": true,
 "showRange": true,
 "showLabel": true,
 "angle": 240,
 "min": 0,
 "medium": 33,
 "high": 66,
 "max": 100,
 "bands": {
 "low": {
 "label": "Low",
 "color": "#B22222"
 },
 "medium": {
 "label": "Medium",
 "color": "#ffa500"
 },
 "high": {
 "label": "High",
 "color": "#008000"
 }
 },
 "step": "TotalActivities_1"
 }
 },

In order to make it dynamic, we have to change the JSON of the widget where we have the parameters “medium”, “high” and “max”. Find that part and add the binding:

"{{cell(StepName.result, 0, "CompareTableColumn").asString()}}"

It should look something like this:

"exploreLink": true,
 "showValue": true,
 "showPercentage": true,
 "showRange": true,
 "showLabel": true,
 "angle": 240,
 "min": 0,
 "medium": "{{cell(DynamicBreakpoints_1.result, 0, "B").asString()}}",
 "high": "{{cell(DynamicBreakpoints_1.result, 0, "C").asString()}}",
 "max": "{{cell(DynamicBreakpoints_1.result, 0, "A").asString()}}",
 "bands": {
 "low": {
 "label": "Low",
 "color": "#B22222"
 },
 "medium": {
 "label": "Medium",
 "color": "#ffa500"
 },
 "high": {
 "label": "High",
 "color": "#008000"
 }
 },

Other changes

As mentioned above you can modify other properties of the widget like label and colors. Have a play with the possibilities.

And now I end up with this final dashboard.

UPDATE 9. August 2018

Post Summer18 release the steps outlined in this blog is no longer sufficient. Please follow the additional steps outlined in the blog “How to Make the Gauge Chart Dynamic Again”.

Written by

Rikke Hovgaard

I have worked with CRM Analytics since 2015 (back then Wave). I started this blog to help make the journey to master CRM Analytics easy by documenting tips, tricks & cool solutions.

See author's posts