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:

image

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

image

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)!

image

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):

image

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:

image

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)

image

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:

image

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:

image

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):

image

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

image