3 Replies Latest reply on May 30, 2012 5:24 PM by Jonathan Drummey

# Bar Chart Sub Category Table Calculation

I am in the education industry and have data that essentially looks like:

Class ID
Acct
Total_Class_Acct

1

1 StrategyAA3
12 StrategyAA3
13 StrategyAA3
22 StrategyAU2
31 StrategyAA3
33 StrategyAA3
42 StrategyAA3
53 StrategyAU2

Where each class ID has one Acct value (every Class id 1 row has an Acct value of AA). I want to display a horizontal bar chart with the Answer then Acct as the rows and a percentage calculation in the column. The percentage calculation I need to do is (Count Acct Answer/Count Specific Acct per classroom) , so the 1 Strategy AA bar would be 2/3 (2 AA Strategy 1's / 3 AA Class ID's). This would basically tell me what percentages of AA classes used this Strategy.

I figured out the denominator for each Acct type and plugged that number in the Total_Class_Acct column. The above example data has 3 AA Classes and 2 AU Classes. Creating the calculation COUNT([Answer])/MAX([Total_Class_Act]) gave me the value I needed. However using this calculation doesn't let me filter by any of my other dimensions like Region, Grade Range, etc. I would like to use a Table Calculation to achieve this.

Here is the link to my dashboard: http://public.tableausoftware.com/views/ClassroomObservations/HelpDashboard?:embed=y

It shows the correct percentages that I cannot filter on the left and the incorrect percentages that I can filter correctly on the right.

I appreciate any help or opinions, thanks.

• ###### 1. Re: Bar Chart Sub Category Table Calculation

Hi Sean,

I looked at this workbook and I can diagnose what's going on but I'm not sure how to help you.

On the All Regions Instructional Strategies view, the calculations are aggregation calculations. Any normal filters applied to the view will cause Tableau to exclude them from results, changing the calculations.

On the Help view, the Percent of Total on SUM(Number of Records) table calc won't return anything like the same amount as the prior view because it is summing the number of records and then doing a % of total on that, whereas the other calculation is using the MAX() of one of the fields. I'm not clear on what you mean by "filter correctly".

When you say filter by, does that mean you want to exclude certain members of dimensions from the view before doing the calculations, or hide them after the calculations? Can you give me an example of a filter?

Jonathan

• ###### 2. Re: Bar Chart Sub Category Table Calculation

Johnathan, you are a support forum rockstar, I think you have helped me with all of my questions.

What I meant was I want to be able to apply filters to other dimensions such as Region, Grade Range, and Content Area. I guess I shouldn't have said filter correctly, since neither bar chart is getting after what I want.

Right now on the AA bars (Green) in the All Regions Instructional Strategies Chart, it's dividing the number of times each strategy was observed by 2,164 because that is how many AA classes were observed. It's getting this value from my "all_account" column in the data. This column has 5 different values corresponding to the number of classes for each colored category.

I added a region filter to my viz. Now if I filter to region 1 it's still dividing the number of times each strategy was observed by 2,164, and I want it to divide by the number of times that strategy was observed for AA classes in Region 1 (929).

Right now I have a static viz and I want to be able to filter by Region, Grade Range and Conent Area. I hope this clarifies and that their is a solution.