4 Replies Latest reply on Jul 21, 2016 8:14 AM by Eliran Epshtein

    Tableau code

    Eliran Epshtein

      Hi Everyone,

       

      I was wondering if anybody here could help me with the following problem:

      I've created a calculated field in order to have a weighted average of b based on a: sum(a*b)/sum(a). When one of my "a" measurements is null, I'm still getting the right weighted average since the null is both in the nominator and the denominator: 

      [null*b1 + a2*b2]/[null+a2]= [a2*b2]/a2

      Yet, the problem is when one of my "b" measurements is null:

      [a1*null + a2*b2]/[a1+a2]= [a2*b2]/[a1+a2]

       

      Is there a way to avoid this wrong calculation in Tableau code? something like

      IF b = NULL THEN SUM(a*b)/SUM(a - sum("a where b is null"))

       

      Thank you in advance for your help,

      Eliran