[Update] Found a new way of doing the same. It is by pivoting. Check out my video.


[Measure Names] is sitting in the Dimensions shelf in Tableau. But it is not a true dimension, because we can't do table calculations along it for example. So we have to resort to some unusual technique to make it a dimension.
Someone got a single row of data with multiple columns of which most are measures. Those are the result of the upstream filtering and aggregation. He doesn't have much authority in changing the upstream logic. He asked me this:

"How to display them in bars (one measure per bar) with tooltips showing each measure's % of total over the sum of all the measures."

The requirement is not so obvious for Tableau implementation. It requires some sort of table calculations.

Note that the single row data is not in an Excel file. It's rather the result of a database query. So, we can't transpose or pivot it easily, because the data source editor in Tableau has got that pivot option for Excel data only.

Here is a simple solution I gave him which met his need.

1.Create a single column [MetaName] in Excel which lists all the measure names.
2.Import it into Tableau and use it as the primary data source "OneColumn".
3.Create a calculated field [MetaMeasure] like
The above can be done by drag-n-dropping the measures from that OneRow data source to the calc field editor. Note that we have to wrap [MetaName] with ATTR(). Ignore the popup message by Tableau BTW at this step.

Now we have a two-column table. But still, we can't calculate % of total using the quick table calculation in right-click menu. Possibly, it is because it involves a secondary data source.

4.We have to create the formula manually. Here is the formula for calculating the % of total for each measure, computing along [MetaName]:
  • [MetaMeasure]/Window_Sum([MetaMeasure])
Put this in the tooltips or label shelf and we will see the correct values. An example is included. Click the image below to view or download the interactive version.
A few side notes here:
1.Between primary and secondary data sources, we don't need any relationship in data dimensions. The reason is we are working at the aggregation level, not at the level of details.

2.This is an extreme example with a single row of data. Actually, when there are more rows, all the aggregations should be done within the secondary data source. Then create a single column to glue the aggregated measures together.

The technique we used here is called data reshaping or scaffolding. I have applied the same trick to create this Funnel chart:
Towards a Perfect Funnel Chart

More on data scaffolding
Data Scaffolding in Tableau
6

View comments

  1. What if I already have a primary datasource?

    ReplyDelete
    Replies
    1. Suggestion: Make up a scaffold table like above to blend with your current "primary source". This will make it a secondary source. Search this blog for data scaffolding techniques.

      Otherwise, send me the workbook if you can. Will have a look.

      Delete
  2. is it ever possible to use measure names as a specific dimension in table calculations. i have 3 measures in measure names and values. I need to do a specific table calc to check the last value vs first for each of the measure. so i need the measure in compute by somehow? any thoughts

    ReplyDelete
    Replies
    1. Yes you can. Just create a scaffold table that includes all the dimensions you need. Then map the values to the scaffold table. See if I made it clear.

      Delete
  3. Great article! Could you please elaborate on: "2.This is an extreme example with a single row of data. Actually, when there are more rows, all the aggregations should be done within the secondary data source. Then create a single column to glue the aggregated measures together."?

    What relationships do we need to make in this case?

    ReplyDelete
    Replies
    1. Here I meant, when there are main category and sub-category, we need to
      1.create both main category and sub-category of measures in the scaffold table.
      2.aggregate the measures at the sub-cat level and bring them to the scaffold table.

      Delete

(Refresh the page if you want to view the gif image multiple times. Or go to Tableau Public and click the button at the top-right corner.)

Jake and I collaborated on a dashboard. He told me that he learnt a way to create an in-place help page in Tableau. He first saw it at a conference somewhere and couldn't recall who the speaker was. So I am blogging here about it but the credit goes to somebody else. If anyone knows who the original creator is, leave a comment below.

The key idea is to float a semi transparent worksheet on top of the dashboard, where a help text box is strategically placed on top of each chart. This way, we can explain how to view each chart and what data points are important, etc. This worksheet is collapsible by a show/hide button. 

Below I would like to show how this worksheet can be constructed.

1. Sheet with a single data mark.

  • Double click the empty space in Marks panel and add two single quotes. Make the null pill a text label. This creates a single null mark.
  • Set the view as "Entire View"

2. Create an show/hide button

  • Go to the target dashboard
  • Drag a floating vertical container to the dashboard, making it cover all the area of interest.
  • Drag the Single Null Mark sheet and drop it into the above container. Hide the sheet title.
  • Create an open/close button for the container and place the button at the top-right corner.

3. Add annotations

  • Format the sheet background opacity as 70% in the layout manager             
  • Select area annotations and place them anywhere of interest. 
  • Write help text and format it to highlight important messages.  
  • The text can serve as functional guide and/or insight guide.

Here is an example. Feel free to download the workbook and explore. Click the "i" button at the top-right corner to view the in-place help. 

0

Add a comment

Blog Archive
Loading
Dynamic Views theme. Powered by Blogger. Report Abuse.