Hi everyone,
I'm new to the forum but have just started self-teaching on Excel Pivot, its an exciting new world, but also running into some frustrations around what I feel should be a simple thing.
Context:
1. My source data is made up of student, campus, demographic columns and then a number of columns for "Risk Factors" (Family Violence etc.) these are filled with 1s and 0s by our School Wellbeing Team
2. I am trying to build a dashboard to allow our Wellbeing Director to easily visualise different patterns in this data (look at prevalence of different risk factors in our cohort, slice based on gender/cultural background/campus/year.
What I need:
1. I need the risk factors to present as a % of total risk factors rather than absolute numbers, this will make our data more accurate because, in absolute numbers female students may have far higher instances of a particular risk factor but this may just be because we have more female students, so it needs to be a relative number rather than an absolute one. I hope that makes sense.
Problem
1. I have set up the pivot chart with risk factors as values, Year as column and Campus/Gender/ATSI (Aboriginal) as filters and then made values "sum" rather than "count" so it doesn't include 0s in the total number.
2020-05-15 09_09_44-RD Tidy Wellbeing Risk Factor Data (Autosaved).xlsx - Excel.png
2. I am trying to turn the numbers into % of the column total but when I click percentage of Column total in Value Settings it doesn't seem to refer to the column on the Pivot table
Percentage of Column Total.png
3. I get a different result when I try % of Parent Column
Percentage of Parent Column Total.png
4. I get another result when I try % of "Total"
Percentage of Total.png
The result I should get for the top Risk Factor (Family Dysfunction) against total risk factors for that year (Highlighted in yellow at the bottom) is 17.9%
I have attached a sample workbook set up like my main workbook (the data is deidentified for obvious reasons).
I'd be greatly appreciative of any clarification or help, the suggestions you make and assistance you provide will have a meaningful impact on creating a data driven school environment to target the most overepresented risk factors among our students to try and improve things for them. I'm not familiar with macros or vbas or anything, so hopefully it can be solved within vanilla pivot which I'm still early in my journey with.
Bookmarks