1 Reply Latest reply on Oct 30, 2013 10:51 AM by Matt Lutton

    Wrong AVG / SUM calculation results caused by left join

    Arnold Siteri

      Hi,

       

      I'm using the following tables in my report:

       

      dim_contact table:

      contact_idturnover
      176
      2122
      3243
      4156

       

      fct_clicks:

      contact_idcategory_id
      1c1
      1c2
      2c1
      3c2
      4c3

       

      dim_category:

      category_idcategory_name
      c1Category1
      c2Category2
      c3Category3

       

      and my calculated field looks like:

       

      IF [Parameter] = 1 THEN SUM([turnover])

          ELSE [Parameter] = 2 THEN AVG([turnover])

      END


      I need the sum/avg (turnover) of contacts clicked in "Category1 and Category2", the expected result are:

           sum=441 and avg=147

      but i've got:

           sum=517 and avg=129,25


      Has anyone any idea how should i avoid the turnover (76) to be counted twice?


      Thanks in advance!


      Arnold