2 Replies Latest reply on Jan 22, 2015 8:59 AM by Todd Cummings

    Determine "Grand Total" when using 2 parameters and multiple calculations

    Todd Cummings

      Hello,

      I'm trying to come up with the weighted average / "Grand Total" on some calculated fields and coming up short.  The attached workbook shows contains parameter controls and calculations and the totals on the calculated fields are blank..  How should I change my approach / parameters / calculation in order to come up with the values for the fields in yellow?

       

      "Exp_per_KG" field is calculated.

      "Exp_per_KG_03_TF" is using a parameter followed by an IIF statement

       

      thanks,

      Todd

       

      Picture1.png

        • 1. Re: Determine "Grand Total" when using 2 parameters and multiple calculations
          Michel Caissie

          Todd,

           

          What you have to understand here is that the Grand Total computes the calculated fields on all

          the rows in the view without taking into account the granularity in the view.

           

          So if we look at  Exp_per_Kg_03_TF  ,  IIF(ATTR([Type]) = "Alpha"  returns  null in the Total computation, because the ATTR() function returns the value of a dimension if it is unique. It's the case for every Type row, but not for the Grand Total because you don't have a unique  Type.

          And as soon as an operand is null in Tableau the result is null.

           

          You can do some gymnastic to overcome this situation. See in the attached.

           

          I replace your calculation with this one.

           

          if MIN( [Type] ) = MAX([Type])

          then

          IIF(ATTR([Type]) = "Alpha",[Exp_per_KG_02_TF],[Exp_per_KG_01_parameter])

          else

          [Exp_per_KG_02_TF (ALPHA)] + [Exp_per_KG_01_parameter (OMEGA)]

          end

           

          if MIN( [Type] ) = MAX([Type])  returns True for the rows in the view because the granularity is at the Type level, but return false for the Grand Total row  because ALPHA does not equal OMEGA.  In this case we SUM each Type measure. So you will have to make  copies of all your measures for each type. Hopefully you wont have to much measures in your real scenario.

           

          I will let you do the  Expense_02_New

           

          Michel