9 Replies Latest reply on Aug 8, 2016 11:43 AM by Mike Sauer

# How to calculate percentages based on distinct values within a single column, using one of the values in that column as the denominator?

Below is a table I've created showing course enrollments by course and attempt number.  In my data, the first time a course was taken has an attempt value of 1.  If they later repeat the course, then they have another row in the data with the same course name but the attempt value=2, and the third time they take it they have a third row for that course and the attempt value=3, and so on.  What I want to show is the 'repeat rates' for each course, for 2nd, 3rd 4th, etc attempts.  In terms of the table below, for example, I want to to show the percentage of students who took BUS-A100 once, and repeated it a second (or third, or fourth, etc. time), which would be 4,525/19,387 (or 670/19,387 for the percentage that took it 3 times, or 98/19,387 for the percentage that took it 4 times, etc.).  So, its a sum of records with a particular value in a column divided by the sum of records with a different value in the same column, where one of the values represents the denominator, grouping along a dimension (course) as we go.  What's the best way to do this?

Thanks,

Mike

• ###### 1. Re: How to calculate percentages based on distinct values within a single column, using one of the values in that column as the denominator?

Mike,

Please see if the attached could be a starting point for you.

I calculated the count of attempts using a Level of Detail calculation:

{ FIXED [Course],[Attempt]:COUNT([Student])}

Then, I calculated the percentage by dividing that count by the max count for that course:

[CountInAttempt]/{FIXED [Course]:MAX([CountInAttempt])}

Probably don't what to show the percent for attempt1, can use:

IF [Attempt]>1

THEN [Percent]

END

and place that on the columns shelf.

• ###### 2. Re: How to calculate percentages based on distinct values within a single column, using one of the values in that column as the denominator?

Hi Swaroop,

Thank you for your quick reply and solution.  I'm having trouble replicating your solution in my project.  Is there any way you can attach the tableau file so I can see exactly how you're doing that?  Sorry if it should be obvious from your text and screenshot...

Many thanks,

Mike

• ###### 3. Re: How to calculate percentages based on distinct values within a single column, using one of the values in that column as the denominator?

Mike,

should have the downloadable twbx for you: 211273repeat.twbx. It should be in v9.0. Which version are you using?

I've reattached it below.

Happy to help further as needed.

• ###### 4. Re: How to calculate percentages based on distinct values within a single column, using one of the values in that column as the denominator?

Oh! For some reason I only saw the screenshot...Thanks for your reply.  I will look at this again soon and see how it works.

Best,

Mike

• ###### 5. Re: How to calculate percentages based on distinct values within a single column, using one of the values in that column as the denominator?

Swaroop,

I still can't get my view to generate a bar graph like you did.  Below is a screenshot of what my view looks like after replicating your steps.  The one thing I see that's different is the pill type of the SUM(CountInAttempt) on the row shelf.  In your view, it's a blue dimension, while in mine it's a green measure pill.  I tried changing the data type and moving it around between Dimensions and Measures, but can't seem to make it change from green to blue.  I also include here a screenshot of the 'Attempt' column properties for you to compare (they are not the same in your data and mine, and may be what's causing my issue).  Another thing I wonder about is my calculation for the CountInAttempt field.  Here is my calculation: { FIXED [COURSE], [ATTEMPT]:COUNT([Number of Records])}.  I used COUNT([Number of Records]) because we try to avoid using the student ID.  However, to test whether that would make a difference, I changed the formula to count the ID ( which would be your 'Student' column), but it made no difference. However, those column properties also differ between your data and mine.  I attached a screenshot of the properties of the ID field as well for you to see.

Do you have any other suggestions or thoughts?

Thank you,

Mike

• ###### 6. Re: How to calculate percentages based on distinct values within a single column, using one of the values in that column as the denominator?

Mike,

I don't think it's too far away.

With respect to the pill type, I had made the Sum(CountInAttempt) discrete.

This is done by right-clicking on on the pill as it sits on the Row shelf and

selecting "Discrete" (please see below image1).

Here is a great discussion on pills and the significance of their colors and

discrete/continuous:

I noticed that your Mark Type was set to "Automatic", but you can force it to

be Bar. (please see below image1)

With respect to the data types of the different fields, I don't think it will affect

it much, but you can right click on a field and Change Data Type there

(please see below image2). If you would like and if there is nothing proprietary to show,

please attach a screen shot also showing the far left panes with all the lists of Dimensions

and Measures. The icon next to them tells the data type.

With respect to the Number of Records, I think it should produce the same result.

Image1

Image2

• ###### 7. Re: How to calculate percentages based on distinct values within a single column, using one of the values in that column as the denominator?

Swaroop,

Thanks for the additional tips and help.  It's still not looking right, as now I'm ending up with percentages that don't look right to me.  I've created a sample data set with just the bare bones fields (attempt and course) and tried to recreate your view with that data and attached it here for you to see.  If you have the inclination to look at it, I'll appreciate whatever additional help you can provide.  For example, looking at the first course (BUS-A100), I expect the percent for attempt 2 to be about 14% (376/2634), but it shows 54%.  Also, I'm not sure what to make of the values of your Show Percent axis with the scale going from 0-2.3.  Can you explain what's going on there?  Thanks again for all your kind attention to my challenge.

• ###### 8. Re: How to calculate percentages based on distinct values within a single column, using one of the values in that column as the denominator?

Mike,

Apologies about that, I think the ShowPercent was working for a different setup.

Instead, you can try:

If [Percent]<1 then [Percent] end

I think that will produce the correct numbers.

• ###### 9. Re: How to calculate percentages based on distinct values within a single column, using one of the values in that column as the denominator?

Swaroop,

Thanks for your reply. After making that additional tweak, it still wasn't working as i expected, so I'm abandoning this endeavor and finding different ways to present the data.  I really appreciate your taking time to try to help a complete stranger.  It's very kind of you.

Best wishes,

Mike