1 Reply Latest reply on May 25, 2016 8:18 PM by Bill Lyons

    Use aggregate value at non-aggregate level for calculations

    Sireesha Avvari

      I have an aggregate calculated field - AvgAmountCalc = SUM(AvgAmount * No.of rows)/No.of rows

      The reason I have to do this as a calculated field is because the value of this field will change based on the filter selections. And to avoid doing average of averages.

      This calculation gives me the correct value for any given filter selections.



      Then, for each row of the dataset, I need to use this value in a condition for another calculated field:

      If SectionCharge-DiscountPrice>AvgAmountCalc Then 1 Else 0 End

      basically i want to count the rows which satisfy the above condition.


      But since AvgAmountClac is an aggregate filed, I cannot use it with the row level fields. Also, I cannot use SUM(SectionCharge)-Sum(DiscountPrice)>AvgAmountCalc because it's incorrect and  I can't count the individual rows.


      I know, passing the parameters to the source query is an option but as far as I know parameter values are not dynamic (if a new status comes through the data, the parameter will not pick it up automatically.) And I don't know whether cascading parameters are possible. (I use cascading filters now - not sure if filter values can be passed as parameters.


      Is it possible to achieve what I'm after in Tableau? I'm using 9.1 version.