1 Reply Latest reply on Apr 17, 2016 11:26 PM by venkata sreekanth

    Grand total isnt calculating correctly

    Dylan Jones

      Hello,

       

      I am working from Tableau public and am very new to Tableau so I apologize in advance. I am not sure how to post the workbook so if you can help me on how to do this I can otherwise I will post screenshots.

       

      I created a calculated field called [ Monthly Total Fees] which takes the annual total fee and breaks this down to a monthly view. The months for the columns indicates when the sales deal was completed.

      From the [Monthly Total Fees] I then create another calculated field [calculation 1] which takes the monthly total fees that should be collected for that month. Here is the formula I used:

       

      IF FIRST() = 0

          THEN [Monthly Total Fee W/ 0's]

              ELSEIF LOOKUP([Monthly Total Fee W/ 0's],0) = 0

                  THEN PREVIOUS_VALUE(INDEX()-1)

                      ELSE [Monthly Total Fee W/ 0's] + PREVIOUS_VALUE(INDEX()-1)

      END

       

      The issue I am getting is that each month calculates correctly for how much each sales rep will collect monthly but the grand total displays only the last monthly total fee:

       

       

      I then created another calculated field that uses Windows_Sum and this displays the correct grand total is displayed on each month instead of the grand total. Here is the formula and screenshot:

       

      WINDOW_SUM([Calculation 1])

       

       

      I have read a lot of articles including these listed below and they helped in getting the calculation but with no success for the grand total unfortunately

       

      Grand total of running sum amounts

      Customizing Grand Totals – Part 2 | Drawing with Numbers