2 Replies Latest reply on Oct 12, 2018 10:52 AM by John Cantu

    Calculating an average when values not always available

    John Cantu

      Hi Tableau Community, need a little guidance here.

       

      I thought that this would be a little easier, but having a hard time figuring out. I feel like I'm almost there as I can see what I want in Sheet 5.

       

      In any case, I have a dimension (Dim) with three attributes (Attr1, Attr2, Attr3). What I'm trying to do is get the Average Value Per Customer and trend it out over the Month dimension. Now, the part I'm struggling with is not including the customers when aggregating if the Dim attributes do not have a Value ($) in that month. Sheet 2 illustrates the sharp drop in the Avg Per Customer at month 6 due to the fact that Attr1 is the only Dim with data past month 6.

       

      Sheet 5 shows how my denominator should look with the Customer Window Sum2 metric.

       

      As soon as I remove the Dim dimension, the denominator becomes inflated, aggregating Customers across Dim (173). I thought I could use an LOD calculation to solve this, i.e. INCLUDE Dim dimension because it's not in view. Still wasn't able to get it to work (Sheet 6).

       

      Target State:

       

      Appreciate your help here.