2 Replies Latest reply on Mar 8, 2012 1:29 AM by Martin Daniel

    Calculated fields over duplicate measures ?

    Martin Daniel

      Hi all,

       

      I am struggling at making something I think it's quite simple.

       

      I have a HUGE dataset that collects actions made by visitors on a website. After some SQL calculations, I come to the following table structure, where metrics are duplicated. 

       

      visitorIDeventIDtransactionIDproductCategoryRevenue
      123123-1567A10
      123123-2567A10
      123123-3567A10
      124124-1568A20
      124124-2568A20
      125125-1569B30
      etc...



       

      I want to get a view with productCategory and revenue.

       

      The correct calculated field would do for each transactionID : SUM([Revenue])/COUNTD([eventID]) and gives 15 as average.

       

      But I cannot find a way to correctly partition so I only have : SUM([Revenue]) / COUNTD([eventID)] per productCategory as TableDown giving 70 / 5 = 14.

       

      I also tried using TOTAL () function to specify my partition but id does not work.

       

      Any hints would be greatly appreciate !

       

      Martin

        • 1. Re: Calculated fields over duplicate measures ?
          Dimitri.B

          The formula can be:

          TOTAL(SUM([Revenue]))/TOTAL(COUNTD([eventID]))

          which uses table calculations, but the trick is in partitioning, which in turn depends on how your sheet is laid out.

          In the simplest case, you don't even need table calcs, see simple avg sheet in the attached. But if you need to show all dimensions on your sheet, then partitioning becomes tricky, and is not that easy to explain.

          Have a look at the way this calc is partitioned in the example, which works for that layout. The general idea was to use advanced option, put category at the top, the dimension with the lowest level of detail present on the sheet (i.e. transaction ID) at the bottom, and compute at lowest level while restarting at the highest. This probably doesn't make sense, but the subject is too complicated to explain in a short post. Tableau has some articles and tutorials on table calculations, but they all fall short (IMHO) of explaining complex cases.

          • 2. Re: Calculated fields over duplicate measures ?
            Martin Daniel

            Hi Dimitri, thanks for your response.

             

            I agree : Tableau falls short at explaining calculations over complex data structure.

             

            I got the idea of using TOTAL() at different level to get the right partition, even if I am not able to replicate it on my big dataset

             

            In my idea I was looking for a way to "neutralize" duplicate metrics so as my average will be 15 for A ((10 + 20) / 2) and not 16.67 as you get. I found a workaround in SQL calculation, by getting a boolean field that displays 1 only once per transactionID. I can then multiply this field by revenue to get the right calculation but it is pretty makeshift..