This is a tough one to figure out. I have a data set with a column for Program Name, Student ID, Visible Minority (yes or no), Gender (Male or Female) and Disability (yes or no). And example of the outcome I am looking to achieve is:
ProgramEnrolment (count of ID)English100Female20Visible Minority10Disabled15
When I load it in Tableau, I'm adding Program, Gender, Disability, and Visible Minority into the Rows, and Measure Names (Enrolment) into the Columns (and updated the filters to only include Female and YES to disabled and visible minority), and I'm getting this:
ProgramGenderDisabilityVisible MinorityEnrolmentEnglishFemaleDisabledVisible Minority2Total2
Its like each time I add a dimension, it adds an additional layer of filtering, rather than JUST giving me the subset of each sub-category.
I'm at a loss as to how to find the solution to what I am trying to accomplish. Any help? I'm pretty new to Tableau, and been through a few online training sessions, but have yet to come across anything that would help me in this.
Thank you.
You can achieve this using calculated fields, but only for one measure > you won't be able to get two columns like you've laid out in the example in your prior post. I'll explain why.
Create a calculated field to represent each 'bucket' i.e. Female, Disabled etc.
I.e. CalcFemale = SUM(IF Gender='Female' then [Total Enrl] end)
CalcDisabled = SUM(IF [Disability] = 'YES' then [Total Enrl] end)
Then move Measure Names to the row shelf, and add the new calculated fields to the measure values pane;
I've only setup two calcs in my example, you can built out the missing calculations. You could also build out another set of calculations to calculated the 'Total WD' as well, but they you will end up with 8 rows per Program.
If your heart is set on having two columns like you've requested, you're going to need to undergo a pretty serious re-shaping of your dataset to support the structure you're actually after. Effectively you need to stack your different 'groups' in the same column. I.e.;
ProgramMetricGroupTotalEnrlTotalWDAdult12Female74Adult12Disabled00Adult12Visible01This is possible without manipulating your file using Custom SQL, but honestly it's not a path I would recommend if you don't know anything about SQL and you're responsible for updating and maintaining this in the future.
