2 Replies Latest reply on May 5, 2017 3:40 PM by Justin Larson

    Aggregation Issue with Percentages Using Calculations for Subtotal/Grand Total Percentage

    Cherie Bardsley

      Hi, all...

       

      We are having an issue with calculating percentages and using Totals when we select more than a single pay period.  Our data looks like this when more than one pay period is selected:

       

      AreaStoreActual PayrollSalesPayroll to Sales Ratio
      1Ft Morgan$104,337$348,397241.44%
      Subtotal$104,337$348,397241.44%
      3Boulder$142,738$461,482251.04%
      Broomfield$148,962$376,295392.12%
      Subtotal$291,700$837,77736.11%

       

      We are using parameters to select our pay period dates.  The Payroll to Sales Ratio with the calculations below is working perfectly when only a single pay period is selected.

       

      We are using the following calculations which are giving us the correct percentage for the Totals, but incorrect percentages for the rows that are not Totals and the Total for Area 1.  Tableau appears to be aggregating the Payroll to Sales Ratios that are not Totals as the pill for the Payroll to Sales Ration has an AGG in it.

       

      TotalFlag Calc

      IF MIN([Store Name]) = MAX([Store Name]) THEN 0 ELSE 1 END

       

      Payroll to Sales Ratio Base

      [2017 Actual Payroll]/[2017 Sales]

       

      Payroll to Sales Ratio

      IF [TotalFlag] = 1 THEN

      SUM([2017 Actual Payroll])/SUM([2017 Sales])

      ELSE SUM([Payroll to Sales Ratio Base])

      END

       

      We are unable to attach a packaged workbook as we pull data for this from three different SQL Server databases using SQL queries.

       

      We tried not using the calculations, but then Tableau summed the percentages in the Total rows--the rows that are not Totals had the correct percentages in this case.

       

      Any help on how to resolve this issue is greatly appreciated.

       

      Thanks,

       

      Cherie B