I'm trying to make a crosstab with subtotals like the image below:
I can create a general table using summarise with the following code:
ds %>%
group_by(Division,Category) %>%
summarise(Total = sum(Headcount),
Female = sum(Headcount[Gender == "Female"]),
FemaleP = sum(Headcount[Gender == "Female"])/total,
Hispanic = sum(Headcount[Race == "Hispanic/Latino"]),
HispanicP = sum(Headcount[Race == "Hispanic/Latino"])/total,
)
But I'm struggling to get subtotals for the division along with subtotals in the blue highlighted row in the screenshot. Any help would be greatly appreciated! Thanks so much.