1 Reply Latest reply on Nov 6, 2013 3:42 AM by Chandra Shekhar Jain

    Average of distinct items

    Jason Olenick

      Hi everyone,

       

      I'm trying to determine how I can average out only distinct records in a report.  Basically, I need to duplicate records to allow for filtering by different criteria as each record may have multiple associated attributes for a particular field.  For example, a unique record may be associated to 1 or more widget groups. My dashboard requirements include the option to filter results based on widget group. When no widget group is selected (user chooses "All"), I don't want my results double/triple counted in the average calculation.  I know this is easier to do if we are doing a count distinct on a particular common id, but I need to count only the value from one of the records when more than 1 widget group is selected in the filter.

       

      I've prepared some sample data to support the attached workbook, where 1 data source has separate records for each widget group (which is how the real scenario plays out in the DB and what is required for our filtering needs) - this is represented in the Multiple Records visualization, and another data source that has the data combined for each distinct item into 1 record, regardless of # of widget groups it is associated to.  This is shown as the Unique Records visualization.  I want to be able to produce the results matching the unique records when the "All" or more than 1 widget group is selected in the filter of the Multiple Records visualization. 

       

      Thank you in advance for any assistance you can provide.