2 Replies Latest reply on Mar 2, 2017 9:54 AM by efua biney

# Customized Subtotals

I'm having trouble summing up the totals in my table. For the most part, I'm totaling by sum. But I want to customize some of the totals to be calculated off of the other numbers in the totals row.

Example:

ALOS = Units Approved / (Discharges - Net Denied). To determine the total for ALOS, I want it to use Total Units Approved / (Total Discharge - Total Net Denied). So it should be 18917 / (1526 - 7) which equals 12.45.

The same goes for Avg Days App Before Denial. It should be Partial App Days / Partial Approved. The total should be 22.90

The same goes for Readmission Rate. It should be Readmissions / (Discharges - Net Denied). The total should be 7.44%.

Right clicking on these specific fields under measure values and selecting 'Total using average' seems to get me close to the numbers I'm seeking. But they're not quite accurate.

The total for

ALOS becomes 12.43.

Avg Days App Before Denial = 21.40

This isn't quite what I want

Is there a way to modify the calculations for specific cells in the totals row?

• ###### 1. Re: Customized Subtotals

Efua,

Change your calculations to be simple aggregation calculations. At the row level, they will compute just for that row, but at the Subtotal and Grand Total level, they will compute over the entire aggregate set of data, which will yield what you want.

[ALOS]

```SUM([Units Approved])/(SUM([Discharges])-SUM([Net Denied]))
```

[Avg Days App Before Denial]

```SUM([Partial App Days])/SUM([Partial Approved])
```

```SUM([Readmissions])/(SUM([Discharges])-SUM([Net Denied]))
```

NOTE: You will need to drag these new calculations back onto the view to change them from SUM or AVG aggregations to AGG (which means "already aggregated"). You may also need to tweak the number format for each.

And then… voila!

I've attached a workbook, but it's in version 10.1.5, so you may not be able to open it (sorry).

• ###### 2. Re: Customized Subtotals

Like a charm, it worked! So much to learn with Tableau

Thanks a lot Jamieson. You're the man!!!!