In last week's blog post I discussed using Parameters for highlighting the background and text. This week I want to talk about another way of highlighting the background with the help of a Parameter and a Reference line!
First you will create the KPI sheet just like in the steps last week, where you have Sales formatted as an ATTR on the Columns shelf as a placeholder. You are really using MIN(0) calculations to have each placeholder column.
Then you perform the changes with changing them all to Text and expand the entire view.
But what if you wanted to automatically change each cell to a background color when its highlighted. See the video below for context.
Display your p_MetricSwap parameter that you created in the previous blog post.
Change the drop-down to display Sales.
Go to the Analytics tab for this KPI sheet. Grab Reference line and select your first column. In this instance it would be on the Sales Column.
On the Edit Reference Line, Band or Box pop-up menu select the MetricSalesHighlight calculation from the Value drop-down.
Select Average as the aggregation.
Remove the label and the tooltip.
Make sure None is selected for the Line under Formatting.
Change the Fill Above and Fill Below to be the same color.
Uncheck the box for show recalculated lines.
It should appear as the print screen below.
Click OK.
Change your parameter to display Profit now. Notice the background color is now gone for Sales!
Drag another reference line this time for ATTR(Profit).
Repeat the same format for this Reference line.
You can now proceed to do this for the last two columns.
The next thing you need to do is to create the clickable header. I did this in a new sheet.
Point to the AllMetricsList data source from last week's blog post.
Place the Value on the Columns shelf.
Create a calculated field with the MIN(0) calculation and put this on the Columns shelf next to Value.
Edit the Axis of the newly formed bars and change the end to fixed of 1.
This will remove any extra space.
Place IsSelectedColor on the Color Marks card.
Change True to Orange and False to Grey.
Place the ValueLabel on the Label Marks card.
Hide the headers.
Rename this sheet KPI Bars Selector.
Place the False and True calculations on the Detail Marks card.
Place both of these worksheets on the dashboard.
Go up to the Dashboard menu and select Filter action.
Set it up as below;
This will remove the highlighting from the selection sheet.
That's it! You should have a sheet highlighting each KPI with a reference line!
You can refer to my Tableau Public dashboard here to see how its configured.
Comments