1 2 Previous Next 25 Replies Latest reply on Aug 6, 2016 7:01 AM by Joshua Milligan

# Filtering by Gender shows 100%? How do I keep percentage of total?

Hello!

When I filter Gender by all, I can see the breakdown of percentage of Females and Males out of the total student body. However, when I filter down to just Female, it shows 100%. Why is this and is there a Global Calculated Field or Parameter I can use to hide the Male percentage but still show the Female percentage of total when I filter by Female (or vice-versa)?

See images here: http://imgur.com/a/XxCu1

I'm fairly new to Tableau...any help would be appreciated!

Brittany

• ###### 1. Re: Filtering by Gender shows 100%? How do I keep percentage of total?

Brittany,

This happens because the percent calculation (which is a special type of calculation in Tableau called a table calculation) happens after the filter, that is once you filter out males, there are only females in the resulting data set, so 100% are female.

You can get around this by using a table calculation as a filter.  That way, all the data is returned for the percentage calculation, but then what you want (males, for example) is effectively hidden.  The code for such a calculation would look something like this:

LOOKUP(MIN([Gender]), 0)

Hope that helps!

Joshua

3 of 3 people found this helpful
• ###### 2. Re: Filtering by Gender shows 100%? How do I keep percentage of total?

Hi Brittany,

You could try a calculated field like this and make it a percentage:

{FIXED [Gender], [Academic Year], [Faculty]: COUNTD([Student ID])}/{FIXED [Academic Year], [Faculty]: COUNTD([Student ID])}

Drop this field into your columns instead of CNTD([Student ID]) and this should work.

Kind Regards,

Stephen

1 of 1 people found this helpful
• ###### 3. Re: Filtering by Gender shows 100%? How do I keep percentage of total?

Hello Stephen,

This worked! Thank you so much!

Cheers,

Brittany

• ###### 4. Re: Filtering by Gender shows 100%? How do I keep percentage of total?

No problem

• ###### 5. Re: Filtering by Gender shows 100%? How do I keep percentage of total?

Hello Stephen,

Just wondering if you know the answer to this one. After I used the formula you provided, I clicked on Faculty to open up Credential Name (it is a hierarchy) and noticed that for each credential, the percentages are the exact same. What do I have to do for it to update for each level of the hierarchy?

Images here: Tableau: Hierarchy? - Album on Imgur

Thanks!

Brittany

• ###### 6. Re: Filtering by Gender shows 100%? How do I keep percentage of total?

That's because we are using a level of detail calculation. So for every extra 'level of detail' you want the calculation to work for you will need to add this to the calculated field. So in this case:

{FIXED [Gender], [Academic Year], [Faculty], [Credential Name]: COUNTD([Student ID])}/{FIXED [Academic Year], [Faculty], [Credential Name]: COUNTD([Student ID])}

And so on... Does this make sense?

• ###### 7. Re: Filtering by Gender shows 100%? How do I keep percentage of total?

Hello Stephen,

Yes that makes sense. I did try this before, but noticed that at the Faculty level, the percentages are over 100%. And when I go down to Credential Name, they appear like this:

Is it computing as the Faculty is 100% and each Credential within that Faculty adds up to 100%? Previously it was computing going across, but I don't see that option available now?

Sorry for all the questions! Thank you for your help!

Brittany

• ###### 8. Re: Filtering by Gender shows 100%? How do I keep percentage of total?

Hi Brittany,

I've investigated this further and don't think it's possible to open up the hierarchy and maintain the calculation. I've just experimented with Joshua Milligan's method and it works perfectly for me with the hierarchy so maybe give it a go. I think I was over complicating things with the LoD calculation. The simplest answer is always the best!

Kind Regards,

Stephen

• ###### 9. Re: Filtering by Gender shows 100%? How do I keep percentage of total?

Hello Stephen,

Thank you again for looking into this. It does work very well on the one sheet (and with the hierarchy), but I am assuming there is no way to make it global? The reason I am looking for this is mostly aesthetic with my dashboards. If we are filtering to get a full picture on our Female student body, for example, I want to have the visual look nice by still showing the proper percentage breakdown and excluding the male percentages- while changing all of the other sheets (intakes, academic load etc) to reflect females only.  If there is a way to make this global that would solve my issue completely!

Thanks!

Brittany

• ###### 10. Re: Filtering by Gender shows 100%? How do I keep percentage of total?

Hello Joshua,

Thank you! This works for the sheet perfectly (I have a hierarchy too and it works with that still). Do you know of a way to make it global? I want to filter to Females only, for example, and have it affect my other sheets too. If you know of a way to do this, that would be great!

Thanks!

Brittany

• ###### 11. Re: Filtering by Gender shows 100%? How do I keep percentage of total?

Sure!

Since it is a table calc filter, it can't be global across sheets.  This is because table calcs are specific to the sheet in which they are used.

But, a possibility would be to use a parameter. The good thing is that you know the domain of values for gender, so if you created a parameter with a list of values consisting of "Male" and "Female" (and expand to other values like "Unknown" if they exist in the data), then you could alter the calculated filed above to something like:

LOOKUP(MIN([Gender]), 0) == [Gender Parameter]

This will give you a True/False result and you can filter to keep only the True values.  Simply include this on any sheet you want and changing the parameter value will refresh all of them!

Hope that helps!

Joshua

• ###### 12. Re: Filtering by Gender shows 100%? How do I keep percentage of total?

Also, by default, the table calcs are computed Table Across (or Down) and that will probably work 90% of the time, but depending on the setup of a specific sheet, you may need to edit the table calculation on Filters (right click the field / use the drop down menu) and make sure it is addressing by all the dimensions (in Tableau 10, you would choose Specific Dimensions; in any version before that, use the drop down in the Edit Table calc dialog to select Advanced and move all dimensions to the right - Addressing)

-Joshua

• ###### 13. Re: Filtering by Gender shows 100%? How do I keep percentage of total?

Hello Joshua,

Apologies that I have so many questions! I have done what you said and it works for the Gender sheet that I have. However, when I put the calculated field into another sheet, it only gives me the option to select True? And when I deselect True, it does not change it on the Gender sheet? Is there also a way to rename True to read as one of the genders?

I'm very new to this, so I appreciate you helping me out-again, sorry for all the questions! I'm sure it would be easier just to send the workbook, but its a bit tricky with student information so I'm not really able to do that.

Brittany

• ###### 14. Re: Filtering by Gender shows 100%? How do I keep percentage of total?

Brittany,