BusinessObjects Board

How Best to Display Crosstab Percentages?

I have some basic HR data that I want to display in a crosstab. Here is what my data looks like.

image

If you want to take a crack at this here is some SQL (SQL Server syntax) you can put in a free-hand SQL query.

SELECT
‘Individual Contributors’ AS [Employee Group]
, ‘Female’ AS [Employee Gender]
, 22 AS [Number of Employees]
UNION SELECT ‘Individual Contributors’, ‘Male’, 22
UNION SELECT ‘People Managers’, ‘Female’, 2
UNION SELECT ‘People Managers’, ‘Male’, 2
UNION SELECT ‘Management Council’, ‘Female’, 0
UNION SELECT ‘Management Council’, ‘Male’, 2
UNION SELECT ‘Exec. Leadership Team’, ‘Female’, 0
UNION SELECT ‘Exec. Leadership Team’, ‘Male’, 1

What I want is to display not just the Number of Employees, but also the percentage of employees within Employee Group and Employee Gender as well as the Total. Here is what I came up with initially.

image

So we have four data elements for each intersection of Employee Group and Employee Gender.

  • Upper-left = Number of Employees
  • Upper-right = Number of Employees / Total Number of Employees within Employee Group
  • Lower-left = Number of Employees / Total Number of Employees within Employee Gender
  • Lower-right = Number of Employees / Total Number of Employees

I did some Googling to see if I could find a better way to present this data. I came across this YouTube video on Interpreting percentages from a cross tabulation table in SPSS. SPSS looks to be a IBM tool which does not really matter, but I thought the results were an interesting alternative…

So I built a similar table with my data…

I like the first option because it is so concise, but it does not have the benefit of the explanatory labels the second option does.

I would like your input on what you prefer and why and/or any recommended adjustments. Or perhaps you have a completely different approach that would be even better. Please share.

Thanks,

Noel

The primary question isn’t what looks best. The question to ask is which metrics are important to the users? What’s being measured/monitored/tracked and why? Each of the numbers tells a different story.

without any labels your first solution is useless :roll_eyes:

in the second table the sum of gender totals count/totals is wrong :yum:

Maybe the use of the word “best” is not correct. The users do not know what is possible and I am trying to show some alternatives. What they have asked for is one crosstab for each percentage, which to me is just too much because I would be repeating the labels and Number of Employees figure.

So you would prefer the second approach? As I mentioned in another comment what the users have asked for is three separate crosstabs; one for each percentrage.

Fixed Gender Totals

second approach looks good.
maybe swapping first and second column is an alternative.

Why have the users asked for separate crosstabs? Do they have a reason for needing them separate? If they want/prefer separate crosstabs, give them separate crosstabs.

If they just don’t realize the data can be combined, show them your different options and let them decide. Why the need to decide for them? Engage the users and get their feedback.

I will show them the options I have come up with. The whole point of this question was to see if anyone had come up with a different way to display this data so I could present another option to the users.

Reporting back here. The users opted for the multiple crosstab option purely because it was easier to explain. That’s what we’ll go with.

Thanks

1 Like