Product: TIBCO Spotfire®
How to show percentage of total (and other data) on Tree Map visualization labels and tooltips.
By default the labels of Treemap visualizations will just include the value of the columns defined in the Hierarchy (Treemap Properties > Hierarchy). Most often this is a categorical grouping like 'Team', 'Project', 'Name', etc. But it is also possible to show data in the labels, such as the percentage of the size of that rectangle with respect to the total area of the treemap (percent of total) similar to what might be seen on stacked bar charts showing the percentage of bar segments (Bar Chart Properties > Labels > Types of labels > Bar segments: Percentage).
You can edit the expression used by the 'Hierarchy' axis of your tree map and actually perform the mathematical calculation of the percentage there. Access the expression used for the Hierarchy here:
- 'Treemap Properties' > 'Hierarchy' > Right click on the column selector and choose 'Custom Expression'
<[TEAM]>This will result in the labels showing just the values of the [TEAM] column in each of the rectangles. For example: N.Y.
But you can also perform calculations on the data and display the results as a string to show data in the labels. For example, this will show what percentage each team contributes to the total [HOME_RUNS]:
<[TEAM] & " (" & (Sum([HOME_RUNS]) OVER ([TEAM]) / Sum([HOME_RUNS]) * 100) & "%)">
This will result in the labels showing the values of the [TEAM] column in each of the rectangles with the addition of a percentage. For example: N.Y. (9.16%)
- The [TEAM] column is still used so the value of that column will still be displayed.
- & and the values in quotes are concatenating strings so that we can format our results in a readable way.
- This expression '(Sum([HOME_RUNS]) OVER ([TEAM]) / Sum([HOME_RUNS]) * 100)' is calculating the percentage home runs per team with respect to the total.
Note: This will not work for Hierarchy columns.
Tooltips:It is also possible to perform similar calculations on the total data set in tooltips. To show the same percentage of total [HOME_RUNS] for each [TEAM]. To add a new tooltip value:
- 'Treemap Properties' > 'Tooltip' > 'Add' > Right click on the column selector and choose 'Custom Expression'
- Use an expression like:
(Sum([HOME_RUNS]) / Sum([HOME_RUNS]) over (All([Axis.Hierarchy])) * 100) & "%" as [Percent of Total]
- Click OK twice to save the changes
See the attached screenshot (Filename: Tree_Map_-_How_to_Show_Percentages_of_Total_in_Label_and_Tooltip.png and Tree Map - How to Show Percentages of Total in Label and Tooltip.dxp) for an example implementation (v6.5).
Note: This will not work with external data (in-database). The data table must be imported and handled in-memory.