Question re: how to disregard duplicate records in source database
Jennifer Brown Mar 29, 2017 1:29 PMHello!
I am currently working with a data set that includes multiple records for a
course in which students enroll, and am trying to figure out how to isolate the
hours in the course for only one instance of the course. For example,
assume there are 4 records for Econ 1. Each record shows that the class
meets for 4 hours. However, since it is the same class, if I add hours
for all four records, my output will show the course meeting for 16 hours
instead of 4. One approach to solving this would be to sum all the hours
and divide by the number of records (e.g.,[SUM OF HOURS] / [COURSE NAME] where
the course name appears four times. However, [COURSE NAME] is a
dimension, and [SUM OF HOURS] is a measure. I cannot figure out how
to divide a measure by the sum of distinct records within a dimension. (I
have in the past used COUNTD but I understand Tableau no longer supports that
function.
I have attached a packaged workbook. Relevant fields in my attempt so
far are as follows:
[C.Sum Hours by Unique Mtg Room and Time]  calculated field as
follows: sum([Mtg Hrs Per Week])/ ([Unique Physical Meeting])
[This is not a valid calculation because it is dividing a measure by a
dimension.
Unique Physical Meeting  the ID assigned to each record that meets at the
same time in the same place each week. Each record has a number of
meeting hours associated with it. As
with the example above, there could be 4 Unique Physical Meeting records that
are exactly the same, each with total meeting hours for the class. To get the correct meeting hours, we need to
sum meeting hours for all 4 records, and then divide by the number of identical
records.
The relevant worksheet in the packaged workbook is entitled “Southeast.”
Any thoughts about how to approach this problem, either based on the initial
solution outlined above, or any other method, would be greatly appreciated.
Thank you!

TAB PCC Workbook01.twbx 24.6 MB