Sometimes we have a bar chart with lots of rows and we want to quickly assess which rows surpass some threshold like a Sales target. And sometimes that target changes.

Enter the dynamic benchmark. One we can change the value of and have the table update on-the-fly.

This dynamic benchmark allows us to compare sales between categories and also determine which categories may be underperforming.

So, let’s get started!

Step 1

Let’s create the basic chart – drag Category onto rows and Quantity onto columns.

Create a parameter called quantity benchmark with the settings shown below. This is what we’ll be changing on the fly to update the table view.

After creating the parameter, right-click on it and select Show Parameter to bring it up on the chart. Then select the down arrow for the parameter options and change it to a slider – just so we can easily change the benchmark value


Step 2

Next, we need a way to indicate which bars on the chart are above or below the benchmark value. For this, create a calculated measured as follows:

This will result in a true or false value. Once created, drag the measure onto colours.

Step 3

Now, we’ll create a reference line to show the benchmark value on the chart. Right-click on the Quantity axis and create a reference line as follows – using the Quantity Benchmark parameter as the Value.

And done! Now we have everything we need – simply use the parameter slider to change the benchmark value and watch as the chart bars change colour based on which side of the benchmark reference line they fall.

The Data School
Author: The Data School