4 Replies Latest reply on Aug 27, 2012 2:46 PM by Tracy Rodgers

    Calculating Group Totals or Sub-Totals That Are Not Impacted by Filters?  Pass Through Functions?

    Mark Strand

      Is there a way to "GROUP BY" with the RAWSQLAGG_REAL function?  Or is there another way to get an aggregate total into a table that is the sum of the entire data source but grouped or sub-totaled on a dimension (or dimensions), and not impacted by filter selections?

       

      In the attached workbook that contains vehicle sales data by geographic region (Nielsen DMAs), I am trying to compute a brand share of sales by region.  I'd like to be able to filter between individual brands and have the brand share display correctly for each region, regardless of filter selection.

       

      To do this I need the total of all sales by region:  Brand % Share of DMA = (Individual Brand Sales in DMA)/(All Brand Sales in DMA).

       

      In my attached workbook, brand share is showing at 100% because when I filter to an individual brand the "all brand total" attempted with the TOTAL function is reduced accordingly.  I have a version of this workbook connected to a CSV file and I tried the RAWSQLAGG_REAL pass through function.  I was able to bring an aggregate total for the whole data set into the tableau table, but could not figure out how to get that total to  "GROUP BY" DMA, or to SUM OVER (PARTITION BY DMA) -- SQL syntax I am familiar with from working in other tools.

       

      Is there a way to accomplish what I am trying to do?

       

      If I filter to, say, Toyota, I need one column to show a Toyota-only sales count for each DMA and another column to remain a constant total of all brands sold by region...

       

      Thanks in advance for any help.