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

# Calculated fields over duplicate measures ?

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 ?

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 ?

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..