Dynamic Chart Markers in Tableau CRM

It’s not unusual when working with data, to want to emphasize some information or key points when looking into charts in a dashboard. TCRM chart markers are very handy when you want to accomplish this. The problem is that you have to create them manually, and they are not dynamic. Or are they? Let’s take a closer look at this feature and how to make them dynamic, making it easy to see at-a-glance where data key points are, and get you a little closer to gaining that bird’s eye view on your business.

What is a Chart Marker?

Chart marker is a feature in Tableau CRM dashboards/lenses that can be used to draw attention to one particular metric in a chart. It can be shown open, in a form of a tooltip with relevant metrics (and/or custom text) inside, or closed, displaying just the mark itself. 

Caveats of Chart Markers and our Approach

We need to take some things into consideration when working with chart markers: They aren’t available for all chart types in TCRM (gauge chart types don’t allow chart markers). Also, since chart markers are created manually, this technique will only work for a fixed number of data groupings, like fixed picklist values or the number of months in a year filtered date chart (we’ll have to create a chart marker for each and one of the picklist values or month name in this example), or to display the best and worst (2 values) sales performance states in a map chart. 

Let me explain this further as we build our dynamic chart marker. 

Dynamic Chart Markers – Use case

Let us say that we want to highlight states with the highest and lowest Opportunity amount in a map chart. I’m using Opportunities dataset created through Out of Box ‘Sales Analytics’ app. You can choose to use this dataset or can use any other dataset which has got at least 1 measure column and at least one geographical column, like BillingState. 

Note: You can also follow this tutorial to create Sales Analytics app in your org. Please try out this exercise in your personal org or any other org where you’ll not affect day-to-day activities in TCRM.

Now that we’re ready with our org and dataset, let’s build dynamic chart markers.

Build Dynamic Chart Markers

In this section, we’ll go through the steps to create dynamic chart markers. I have split these steps into five broad categories. Let’s take a look at each category and see dynamic chart markers in action at the end.

Step 1 : Query to display sum of Opportunity Amount by State

We’ll start by creating a new blank dashboard. In Analytics Studio, and then, let’s create a query to display opportunity amount grouped by ‘Account.BillingState’. I’ll use the map chart as my chart and the US States as the chosen map. The query will be like this:

q = limit q 2000;
q = load "opportunity";
q = filter q by ('Account.BillingState' != "N/A" && 'Account.BillingState' is not null);
q = group q by 'Account.BillingState';
q = foreach q generate 'Account.BillingState' as 'Account.BillingState', sum('Amount') as 'sum_Amount';
q = order q by 'sum_Amount' desc;

You can also choose to apply some conditional formatting for better chart readability. Once you’re happy with your changes, drag this query on the dashboard canvas. 

As you can see, even if we set bright different colors in the conditional formatting, it’s hard to identify which state has the greatest and the lowest amount. 

Steps 2 : Create static Chart Markers

Go to the widget properties of the previously created chart widget, and set your chart markers:

1. Click on any state to create the first marker
2. Select your parameters, insert a custom text if you want to. 
3. Select a color to distinguish the highest opportunity amount. 
4. Click in another state and repeat the same step, but now with a different color, to display the lowest amount.

Note: You can find out more about creating chart markers here.

In the next step, we’ll update these markers with bindings for the dynamic nature.

Step 3 : Create a query to identify states with highest and lowest Opportunity Amount

Let’s create another query to display our highest and lowest opportunities amount. For this we’ll use windowing functions to get, in a single query, our highest and lowest states. I am calling this “filter_1” and this is what it looks like:

q = load "opportunity";
q = filter q by ('Account.BillingState' != "N/A" && 'Account.BillingState' is not null);
q = group q by 'Account.BillingState';
q = foreach q generate 'Account.BillingState' as 'Account.BillingState', sum('Amount') as 'sum_Amount', case when (max(sum('Amount')) over ([..] partition by all) == sum('Amount') || min(sum('Amount')) over ([..] partition by all) == sum('Amount')) then true else false end as 'filter';
q = filter q by 'filter' == true;
q = order q by 'sum_Amount' desc;

And our result will potentially look like this:

Step 4 : Make chart markers dynamic

Now the fun part! We are going to make the chart markers dynamic so that we’ll always find states with the highest and lowest opportunity amounts highlighted. In this section, we’ll bind the static “row” parameter of chart marker with the results of “filter_1” query. You can follow the steps to perform the bindings:

1. When in Dashboard Edit mode, click on the map widget
2. Click on “Advanced Editor”
3. In the advanced editor, in the widget’s tab, locate “markers”. You’ll notice that you have a row parameter displaying the state’s names in your code (in this example, LA and KS). This is where we will replace the rows with our “filter_1” query result
4. Create a result binding to replace the state values. The row index 0 indicates the state with highest amount, and 1 indicates the state with lowest amount. 

Your binding can potentially look like this:

State With highest Opportunity Amount –

{{cell(filter_1.result, 0, \"Account.BillingState\").asString()}}

State With lowest Opportunity Amount – 

{{cell(filter_1.result, 1, \"Account.BillingState\").asString()}}

Step 5 : Dynamic Chart Markers in action

Finally, let’s check if our chart markers are really dynamic. Insert some global filters in the dashboard that will affect this chart. Notice that, as you change and filter your dashboard data, your chart markers change the states to display data accordingly. Play a little with the filters, applying different combinations to see the chart markers changing and displaying different states.

I hope you liked this application of bindings in chart markers.

Written by

Phillip Schrijnemaekers

Tableau CRM consultant at riverBI

See author's posts