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.
A work around will be the easiest way. Other than changing the structure of the data.
Have you tried aggregating an extract ? See - Creating an Extract
(You will have to hide Widget Column and aggregate on the num_widgets)
You may have to use two files / extracts and blend data together later.
You may want to try it out.