1 Reply Latest reply on Nov 1, 2015 2:20 PM by Daniel Vincent

    how to do further calculations on Row Totals and Column Totals

    fauzia.jalali.0

      Hi All,

       

      I want to be able to to add all the values across rows and then then add them across Columns for certain months(feb,march,apr)

      then divide them by some number and final Result should display in month of June,july,Aug ..

      Something similar to what I have in my attached Excel file. column P (FY16)

       

      I tried table calculation but it doesn't give me option to divide by any number.

       

      PFA Excel and my Tableau Extract.

       

      Any Help is appreciated.

       

      Thanks!

       

      Zia

        • 1. Re: how to do further calculations on Row Totals and Column Totals
          Daniel Vincent

          Based on what your Excel is showing it seems you are taking the grand total of whatever months are shown then dividing by an arbitrary number.

           

          This can be accomplished with doing a LoD and table calc to put the value in the grand total at row level.

           

          First this LoD will hold the aggregation at the level needed based on whats shown on the viz along with what is filtered.

          ({FIXED [c.MY],[Case Burt Status],[Case Subteam],[Case Tg]:COUNT([Case Num])})

           

          *B/c LoD doesn't allow for aggregations directly in the FIXED portion you have to create the month/year dimension which you'll see in my c.MY

          Screen Shot 2015-11-01 at 3.29.19 PM.png

           

          Then this calc will force that value divided by the parameter of "p.SomeNumber"  while keeping the right value for the column and column grand total:

           

          IF LAST()=FIRST()

          THEN SUM({FIXED [c.MY],[Case Burt Status],[Case Subteam]:COUNT([Case Num])})/[p.SomeNumber]

          ELSE SUM({FIXED [c.MY],[Case Burt Status],[Case Subteam]:COUNT([Case Num])})

          END

          Screen Shot 2015-11-01 at 3.26.42 PM.png