Turning Bar Chart/ Column Chart Into Indicator Widget To Compare Multiple Goals

[intapiuser]

The purpose of this post is to provide the instructions to use a bar or column chart to create a widget where you can compare a value such as revenue to multiple goal levels. 

In order to do this I had to add a slight modification in the Elasticube. There needs to be a value along the X-axis to plot the goals and revenue against. However, if you plot a line chart to one value on the X-axis it will just be a single point. So lets begin by assigning the goals to 3 values (1, 2, and 3) on the X-axis and call this field Key. Then lets add this same field to the table containing revenue but only add the  value '2'. This will put my bar in the middle of the goal lines. 

Below is the custom SQL expression I used to add all 3 values to my X-axis for the goals.

SELECT  a.Amount,
        a.Level,
        1 AS key

FROM [Goals] a

UNION ALL 

SELECT  b.Amount,
        b.Level,
        2 AS key
FROM [Goals] b

UNION ALL 

SELECT  c.Amount,
        c.Level,
        3 AS key
FROM [Goals] c

When you make  a bar/line/area chart and you have multiple values, click on the hamburger for a specific value and and change the series type.

I set the goals to be lines and I used measured values to only sum one goal at a time. 

The X-Axis is just that random key, which is 1,2, &3 for the goals, but only 2 for the revenue. This is so the lines wont be points and will span across the bar. This way when we shrink it the chart will mimic an indicator with goal lines. 

Set the revenue to use a measured value to calculate across a specific goal to avoid a M2M.