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

# Getting incorrect % of row

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

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

Here is how the Table Calculation is being handled:

• ###### 3. Re: Getting incorrect % of row

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

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

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

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

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.

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

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:

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

Mavis

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

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

Hi Guy,

No problem, glad to be of help!

Thanks,

Mavis