[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.
"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
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])
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
What if I already have a primary datasource?
ReplyDeleteSuggestion: 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.
DeleteOtherwise, send me the workbook if you can. Will have a look.
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
ReplyDeleteYes 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.
DeleteGreat 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."?
ReplyDeleteWhat relationships do we need to make in this case?
Here I meant, when there are main category and sub-category, we need to
Delete1.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.