Different Metrics, Different Rows, Different Colors using MIN(1)

Do you have KPI numbers that you want to have a different color scheme for each row? There are several ways to enter KPI (Key Performance Indicators) into a dashboard. In this tech tip here I review how to add different calculations and put all of the text into one Marks card.

In the below blog, I list another way to have different metrics into different rows using the MIN(1) calculation. You can change the color range for the different metrics, but keep in mind you want to have minimal color choices on a dashboard, or it becomes too visually "busy." This tip was modified and expanded from this 2014 Data Psientist article here.

In the chart below I have Segment listed at the top of the chart and for each row I have a different metric. Notice there are different color schemes. If you were to use a Measure Values pill, you would not be able to add the different color schemes for each metric.

There are a couple of other tricks in this article such as renaming your MIN(1) calculation right in the shelf, so you won't have to rename the Axis.

Another trick is to how to move the Segment header to the top of the chart instead of having it at the bottom.

Please note: I will be using the Month over Month calculations I created in this tech tip here.

First I put Segment on my Columns shelf using the Sample-Superstore dataset

I then right-click on the Rows shelf and select New Calculation.

I paste the following formula into this new calculation;

//Current Mo Sales


I click Enter when done.

I then repeat this step for the following 3 calculations;

//Prior Mo Sales


//% Diff


//Curr Mo Profit


*I learned this rename calc trick from Tableau Ambassador Will Perkins!

The dashboard appears as below;

Notice how it created a bar chart for every min calculation. There are also separate Marks cards for each Min calculation.

For the first marks card labeled Curr Mo Sales I place a calculated field called Sales (This Month) Segment on the Label card. I also change the Chart type to Text.

I right-click on this pill in the Marks card to change the format to Currency. I also remove the extra decimal places and format the Display Units as Thousands (K).

I also increase the Font to 20 and make it Bold.

I change the view of this worksheet from Standard to Entire View.

I click to edit the axis of this row. On the Edit Axis pop-up menu, I click on the Tick Marks tab and select None. This removes the 0 and 1 from the side axis.

I proceeded to repeat these steps for the Prior Mo Sales, %Diff and Curr Mo Profit Marks cards with the exception of formatting the %Diff metric as a percentage with no decimal places.

I then remove all of the Borders except for the Pane Row Divider by right-clicking anywhere in the chart and selecting Format.

I also hid the Field Labels for the Columns by right-clicking on them and selecting the hide option.

I also right-clicked on each axis to format the text with 10 as the font size.

To put Segment at the top of this chart instead of the bottom, I right-clicked on the Columns shelf and selected New Calculation.

I typed "Header" for this calculation and clicked Enter. This moved Segment to the top. I formatted the Segment text as Bold with a font size of 20.

I then right-clicked on the "Header" pill in the Columns shelf and unchecked the Show Header option.

Then I added different color schemes to the different marks cards.

I placed the MonthDiff% Segment calculation on the Color card for the Diff % Marks card.

I changed the color scheme to Stepped Color and left it at 2 steps.

Next I put Profit (This Month) Segment on the Color card of the Curr Mo Profit Marks card. I changed it to Green-Blue Diverging color scheme and clicked the box for Reversed. I also reduced it down to 2 steps and then clicked OK.

When all of these steps were complete, I had the chart in the beginning. If you want to download the dashboard, it is stored in my World of Charts dashboard in Tableau Public here.

128 views0 comments