2 Replies Latest reply on May 7, 2014 1:45 PM by Lynn Snow

    Can I aggregate data at a higher level, then calculated summaries based on those aggregated values?

    Lynn Snow

      I am trying to figure out if Tableau can aggregate data at higher levels, that can be used in other analyses based on those aggregated values.  In my mind, this is similar to using a SumIf formula in Excel.  I've search for a solution, but haven't found anything that explains how to do this in Tableau (yet). 


      I am guessing the only answer is to calculate these tables of summarized data outside of Tableau (such as in SQL).  However, Tableau is proving to be such a powerful tool, that it would make sense for it to have this functionality built-in.  The simplest route would be to load in the data at the most granular level of detail, then use Tableau to do ALL the aggregation. 


      Below are two example situations of what I would like to accomplish.  I've attached an Excel file with dummy data.  The example input data provides details for individual tickets of lbs purchased (I work in a recycling business).  Each ticket may have multiple products on it.  I want to load this into Tableau, because it is the lowest level of detail.


      (1)  Calculate % of Total based on an ID field

      For each Product on each Ticket, I want to calculate the % of Total based on Lbs.  Therefore I need to Sum the Lbs for every unique Ticket # first, then I can divide the individual Product Lbs by the Total Lbs per Ticket.


      (2)  Create an aggregated summary table, then calculate averages based on the summarized values

      Using the original data, I would like to see an average of the Total Lbs per Month by Quarter.  However, if my data is at the detailed Ticket level, I can only do averages per Ticket line item at the quarter level.  The data first needs to be aggregated by Month (sum lbs by month) then I want to calculate averages based on these monthly totals. 


      I have many scenarios similar to these two that need a "SumIf" type calculation.