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.
