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?

    Mike Sauer

      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