DV: Displaying data as % (Percentage) Of other column in graphs…

To display data as percentage of, can be defined within the Visualization properties for Tables and pivot Tables. However, the percentage of does not exist for all Visualitzations such as Bar Graphs and thus requires the calculation of these percentage values. Other Visuals like Pie Chart or Donut do support to display the data as Percentage Of.

Taking the following baseline example with Car Accidents by Body Type and if drinking has been involved:

image The percentage Of can be displayed for each column using the Value Tap of the Visualization properties:


This will result in the following view for the same table (as above), showing the percentage of values:


The above setting will only show the percentage of with respect to the same column (distribution within the same column, as black arrow below) and not as percentage of Body Type (Green Arrow below)!


The property section, does have the possibility to change the relation for displaying the percentage to other columns, e.g. Body Type, but this is not calculating correctly within DV (no further inverstigation on this Issue):


As mentioned at the beginning, for some Graphs like Bar Graphs, there is no option to display the data as percentage of values, since this is not the best visualisation to represent distributions and thus the Bar Graphs will only show the absolute values, like:


In order to display the correct Percentage Of in a table or in a graph, it is need to calculated as Percentage Of before hand. The formula for calculating the percentage is:

percentage value = SUM (occurences)  / COUNT (overall)


In order to calculate the correct sum for the occurences, the formula for decoding the attribute field into 1 and 0 has to be done on the data preparation step (on the prepare tab). Otherwise, the SUM will not calculate correctly when using the “My Calculations”, since it will not apply the calculation on each row, but only on the aggregated result as clearly shown below with the bottom red box:


This is because the Case When Formula is not evaluated row-by-row, but only applied after aggregation. Hence, the decoding of values for calculating the percentage must be placed within the data preparation step:


After this preparation, the formula can be defined as shown below (Please note the multiplication with 1.00 to convert to a decimal number to represent the percentage):


This gives the intended result for correct Percentage Of values within a Bar Chart and Table View:



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.