How to display multiple choice fields in presentations/reporting (Excel)?

[Roland Uittenbogaard]

In Short: We use multiple choice fields where customers can select more than one option. How to display multiple choice fields, in numbers or percentages? Is there a recommended industry standard, or does it depend on your own preference?

Longer: Example, suppose you have a field Business with six choices:  Engineering, Liability, Marine, Not defined (Archive), Other, Property. Suppose the original output is as follows (with a total number of tickets = 467):

Because customers can select more than one field you get Engineering but also Engineering;Liability etc as a category. Next, because we only want to show the seperate categories we can use Excel/Power Query to split the column Business and the result is as follows (with a total number of 941 !):

Now if you split/add the categories you get a higher number of 'tickets'. E.g. if you have one ticket where the customer chooses Engineering, Liability and Marine, it is one ticket, but you get a count of one for each, so three in total.

In this case the field Business is a field where customers often choose more than one category and in that case the 'number of tickets'/grand total blows up, in this example from 467 to 941.

Now I can present the result using numbers or percentages. You might want to show how many times Engineering, etc is choosen and show the result in numbers or you can display the result in percentages. If you display numbers, people might ask why the total number suddenly is much higher and you can explain that it is a multiple choice field. If you display percentages people might ask why you move from numbers to percentages in a presentation or report.

This example is only displaying one multiple choice field but you get the same issue for one multiple choice field vs one normal field, and for one multiple choice field vs one multiple choice field.

At first I thought percentages would be better, but in that case half of the tables/charts in your report are in percentages and that looks a little strange.

Is there an industry standard to choose for numbers or percentages? Can you also combine, so choose numbers for fields where the grand total is not much bigger than the original number of tickets, and choose percentages if the number of grand total blows up compared to the original number of tickets?