1 Reply Latest reply on Sep 14, 2018 3:51 AM by Jim Dehner

    Aggregate using calculation (geometric mean or product) in Tableau Prep

    Rustin Reed

      I have a dataset that includes health samples collected from all US mines - they are consistent with environmental samples and tend to have log normal distribution. I need to aggregate samples by each mine site (anywhere from 0 to dozens of samples taken at each mine site) before joining this dataset with others.

       

      My Grouping dimension is the mine id’s, and the Aggregate measure is a concentration or dose. The structure looks something like this (not actual values):

       

      Mine ID, Percent of PEL

      100300, 12.5

      100300, 45.7

      100300,  121.8

      100301, 62.7

      etc

       

      Desired outcome

       

      Mine ID, Geomean of Percent of PEL

      100300, 41.1

      100301, 55.3

      100302, 21.8

      100303, 73.6

      etc

       

      Aggregating using the arithmetic mean would be inappropriate and, while median is an option, I would prefer to use the geometric mean.

       

      As far as I can tell, calculating a custom aggregate type during an Aggregate step is not possible. If I could even get the product of values for each mine id I could, in a separate step, create a calculated field using the product and nth root (from Count).

       

      Are there my options to do this without having to export to another program first, and then import back into Tableau Prep?

       

      Thank you in advance.

        • 1. Re: Aggregate using calculation (geometric mean or product) in Tableau Prep
          Jim Dehner

          Good morning

          Tableau Prep is design as tool to clean, reshape and connect data files rather than an analysis tool - if you could do the geometric mean in excel and then import the excel file you could then join it to your other files in Prep and complete the data cleansing process

           

          Jim

           

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.