3 Replies Latest reply on Nov 22, 2017 1:38 AM by saurabh.c.0

    How to do Conditional sum in tableau

    Sameer Vaghela

      Hi All,

       

      Column1      Column2      Column3

      1                  12                 13

      2                  24                 8

      3                  10                 7

      4                  5                  20

      5                  14                16

      6                  9                   3

       

      What I need is :

      (Sum(Column2)*100)/(Sum(Column3)) when Column1 = 1,3,5,6

      that would be -> (12+10+14+9)*100/(13+7+16+3) = (45*100)/39 = 115.3846

       

      So far I have tried various way

      1. IF [Column1] = [Parameters].[Column1 Parameter1] THEN

      (([Column2]) *100)/(Column3])

      END

      - In this, (Column2*100)/(Column3) when Column1 = 1,3,5,6 is calculating. But, this is not what I want.

       

       

      2. IF STR(ATTR([Column1])) = "1" OR

      STR(ATTR([Column1])) = "4" OR

      STR(ATTR([Column1]))= "34" OR

      STR(ATTR([Column1]))= "234"

      THEN

      (SUM([Signal Strength 3G Gte75]) *100.00)/SUM([Signal Strength 3G Count])

      END

      - Results in null - no value

       

      3. IF ([Column1]) == 1 THEN [Column2]

      ELSEIF ([Column1]) == 4 THEN [Column2]

      ELSEIF ([Column1]) == 34 THEN [Column2]

      ELSEIF ([Column1]) == 234 THEN [Column2]

      ELSE null

      END

      - its giving me sum of all the values, not specific to Column1

       

      4. IF ([Column1]) = 1 OR

      ([Column1]) = 4 OR

      ([Column1]) = 34 OR

      ([Column1]) = 234

      THEN

      ([Column2])

      END

      - Same result as above one

       

      5. (SUM([Column2])*100)/SUM([Column3])

       

      - This works ... but in this case I need to apply filter of column1 and select 1,3,5,6. this is a workaround in my case becuase sometimes I wont have all the values.

       

      6. SUM(IF STR([Column1]) = "1" OR

      STR([Column1]) = "4" OR

      STR([Column1]) = "34" OR

      STR([Column1]) = "234"

      THEN [Column2]

      ELSE null

      END)

      - its giving me sum of all the values, not specific to Column1

       

      Anyone have any idea how to tackle this ... I have tried almost everything !!!