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

    Customized Subtotals

    efua biney

      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

      Readmit Rate = 7.48

       

      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
          Jamieson Christian

          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])
          

           

          [Readmit Rate]

          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
            efua biney

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

             

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