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

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

• ###### 1. Re: Aggregation Issue with Percentages Using Calculations for Subtotal/Grand Total Percentage

Hey Cherie,

It sounds like you may be running in to some of the common issues around grand totals. This thread may help you determine the underlying issue.

• ###### 2. Re: Aggregation Issue with Percentages Using Calculations for Subtotal/Grand Total Percentage

Sensitivity of the data aside, the fact that they are SQL-sourced should not make a difference. You can extract and package the workbook.

In any case, are the two fields [2017 Actual Payroll]/[2017 Sales] in the same data source, or are you blending data on this view?

If they are the same data source, what happens when you use the formula SUM([2017 Actual Payroll])/SUM([2017 Sales]) as Payroll to Sales Ratio without the if statement?