2 Replies Latest reply on Jul 30, 2012 10:42 AM by skip.nyegard

    Row Grand Total not producing expected result

    skip.nyegard

      I am attempting to convert a manual Excel-based worksheet into Tableau and have encountered a problem with calculating a row total.  Attached is the packaged workbook that shows the problem.

       

      This workbook is part of a "days sales outstanding" (DSO) report that monitors aging accounts receivable balances.  Using a weighted average method for calculating this, we assign each open invoice to a "bucket" based on how many days have elapsed since the customer was invoiced.  Buckets are from 1-30 days, 31-60 days, 61-90 days, etc., up to 391+ days.

       

      Each of these buckets is assigned a weighted average days.  For example, invoices in the 1-30 day bucket would be assigned a value of 15.5 days, invoices in the 31-60 day bucket are assigned a value of 45.5 days.  As the invoice ages, the average days value increases.

       

      To calculate the number of days assigned to each bucket, I used a "Percent of Total" table calculation:

       

      SUM([Invoice_Amount]) / TOTAL(SUM([Invoice_Amount]))

       

      Then, to apply the average days to each bucket, I multiplied this table calculation by the average days assigned to each bucket:

       

      (SUM([Invoice_Amount]) / TOTAL(SUM([Invoice_Amount])))*AVG([Bucket Avg Days])

       

      This calculates the correct individual bucket days, but the grand total column does not display the correct total for the row.  The total for first row in the image below should be 27.9, but it is displaying as 42.5.

       

      Any assistance on this would be greatly appreciated!

       

      DSO Example image.jpg

        • 1. Re: Row Grand Total not producing expected result
          Tracy Rodgers

          Hi Skip,

           

          Grand totals and subtotals within Tableau keep the aggregation that is applied to the measure. Therefore, in this example the grand total of the sum of Inv Amt is being divided by itself, thus giving the value of 1, which is then being multiplied by the overall average of Bucket Avg Days. The best way around this is to place the desired values on one sheet and the correct grand total on a separate sheet and place them on a dashboard together. To get the desired grand total, do the following:

           

          1. Create a duplicate copy of Bucket (right click on it in the data box and select Duplicate). Place this on the level of detail shelf.

           

          2. Create a calculated field similar to the following:

           

          IF FIRST()==0 THEN

          WINDOW_SUM(([DSO Calc]),0,IIF(FIRST()==0,LAST(),0))

          END

           

          3. Place the above calculation on the view. Right click on it and select Compute Using-->Bucket (copy)

           

          Hope this helps!

           

          -Tracy

          • 2. Re: Row Grand Total not producing expected result
            skip.nyegard

            Thank you Tracy!  Worked perfectly.

            And thank you for the explanation as to why this was occurring.