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

    Bar Chart Sub Category Table Calculation

    Sean Marcoulides

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

       

       

      Class ID
      Answer
      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
          Jonathan Drummey

          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
            Sean Marcoulides

            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.

             

            Thanks in advance.

            • 3. Re: Bar Chart Sub Category Table Calculation
              Jonathan Drummey

              Hi Sean,

               

              In looking at your data, I'm not sure where you're getting that 929 number, I don't see it in the data at all.  The All Region Number of Classes calc is MAX([all_acct]) so what I did was set up a view called "all_acct" that has the question, id_region, state_acct (group), and all_acct fields on it. For group AA, there's only one value for all_acct and that's 2164. So the view is doing what it's set up to do, and the filters are working, they are returning the number in the data.

               

              Jonathan