9 Replies Latest reply on Aug 13, 2018 3:05 AM by Mavis Liu

    Getting incorrect % of row

    Guy Nishimoto

           I am using the Tableau Desktop 2018.2.

       

      I am counting enrollments by division, so I used count([PIDM]) (PIDM is the student identifier) to get a table like this:

      I want percentages, so I click on   Analysis > Percentage Of > Row.  Everything works fine.  I get this:

      Then I decide I want unduplicated headcounts of students.  So I change cnt(PIDM) to cntd(PIDM).

      Fine:

      BUT WHEN I APPLY THE ANALYSIS ( > Percentage Of > Row), I get a percentage exactly TWICE

      WHAT THE CORRECT PERCENTAGE SHOULD BE.  What is happening?

        • 1. Re: Getting incorrect % of row
          Mavis Liu

          Hi Guy,

           

          Please can you right click on on your green CNTD(Pidm) and select edit table calculation and let me know what the scope and direction is?

           

          Thanks,

           

          Mavis

          • 2. Re: Getting incorrect % of row
            Guy Nishimoto

            Here is how the Table Calculation is being handled:

             

            • 3. Re: Getting incorrect % of row
              Mavis Liu

              Hi Guy,

               

              Could one Pidm be across multiple different CDiv?

               

              Because if that's the case, then that's why the % of total is so high. The count distinct TOTAL is de-duplicated.

               

              For example, two departments ABC and ABCD may both have ID 123

               

              ABC               ABCD

              123                123

               

              For % of total, it would be:

              ABC               ABCD

              100%              100%

               

              Because for each department, the count distinct of ID is 1, but on an overall level (taking both departments into consideration) the distinct count of ID is also 1.

               

              Thanks,

               

              Mavis

              • 4. Re: Getting incorrect % of row
                Guy Nishimoto

                Thank you for the help.  I'm not sure that the explanation works.

                 

                What you are saying would be even MORE true for the enrollment counts.  The same student identifier could turn up possibly multiple times IN a department AND ALSO ACROSS DEPARTMENTS.

                 

                With COUNT(<identifier>), the percentage calculation seems to be work on each number in the row, being divided by the sum of the numbers in the row.  The numbers displayed are treated just as I would expect--as in a spreadsheet.

                 

                Why would unduplicating the counts cause the sum to be exactly halved (or the individual numbers to be doubled)?

                 

                Maybe I'm not understanding your explanation correctly?

                • 5. Re: Getting incorrect % of row
                  Mavis Liu

                  Hi Guy,

                   

                  This is because the calculation is the same on each department level, and then on an overall level.

                   

                  Please could you create a new worksheet and bring in your Pidm into rows and then bring CDiv into rows too?

                   

                  The Pidms must be appearing across multiple departments and that's why you're getting higher percentages.

                   

                  When it's just an overall count level, it's fine because using my example before, even if there 123 appeared in two departments, the count would be 2:

                   

                  ABC               ABCD

                  123                123                        OVERALL COUNT = 2

                   

                  ABC               ABCD

                  50%              50%

                   

                   

                  Whereas using countd you'd get :

                   

                  ABC               ABCD

                  123                123                        OVERALL DISCOUNT COUNT = 1

                   

                  ABC               ABCD

                  100%              100%

                   

                  As you can see, the issue is the overall total.

                   

                  If you like, you could upload the workbook with sample data and I can take a look.

                   

                  Thanks,

                   

                  Mavis

                  • 6. Re: Getting incorrect % of row
                    Guy Nishimoto

                    Hello Mavis . . .

                     

                    Thank you for continuing to explain.  I don't really understand how Tableau does this calculation.  I'm treating Tableau as if it were Excel.

                     

                    Anyway, I'm attaching a workbook with an "abridged" set of data

                     

                    It contains 4 worksheets:

                     

                    1.  A table showing enrollments in 6 divisions across 8 semesters.

                    2.  A table showing percentages of total enrollments in each semester.

                    And everything up to that point works fine.

                    Then . . .

                    3.  A table showing unduplicated headcounts--COUNTD(<student ID numbers>)--in 6 divisions across 8 semesters.

                    And here's where everything goes wrong:

                    4.  A table that's SUPPOSED to show the percentage of total headcounts in each division in a semester.  In other words, a percentage of the column total.

                     

                    I've also put in a dashboard that allows me to show the fourth worksheet and a JPG of an Excel table showing the percentages that actually should be showing in the fourth worksheet.

                    • 7. Re: Getting incorrect % of row
                      Mavis Liu

                      Hi Guy,

                       

                      So yes I see the problem, what  I had said before is true in your case:

                       

                      As you can see, Pidm 711 occurs in multiple different C Div and multiple Terms. However your % of total will only count this Pidm as ONE because it's doing a count distinct of Pidm of the whole dataset.

                       

                      2018-08-10_19h04_28.png

                       

                      So what you need to do is use an LOD calculation, I have used fixed and created this:

                       

                      2018-08-10_19h06_35.png

                       

                      This means for every term and C Div, it is calculating the count distinct of Pidm. Then at an overall level, it is adding it all up.

                       

                      So the final view, I use this new field and I get is this:

                       

                      2018-08-10_19h07_37.png

                       

                      Thanks and please see the attached workbook with this example. The viewsI have created have an 'ML' at the end of the worksheet names.

                       

                      Also more information on LODs here:

                      Overview: Level of Detail Expressions

                       

                      Mavis

                      1 of 1 people found this helpful
                      • 8. Re: Getting incorrect % of row
                        Guy Nishimoto

                        Hello Mavis . . .

                         

                        Thank you VERY much.

                         

                        First, for all the work you put into this.

                         

                        Your responses to my questions--especially your last response--was so well-organized and clear that . . .

                         

                        You have not just solved my problem, but you've given me a much better understanding of how Tableau works (or does not do what I think it's doing).

                         

                        Thank you also for pointing me to LOD calculations.  I didn't even know that I needed to know about them!!!

                        1 of 1 people found this helpful
                        • 9. Re: Getting incorrect % of row
                          Mavis Liu

                          Hi Guy,

                           

                          No problem, glad to be of help!

                          Thanks,

                           

                          Mavis