The formula can be:
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.
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..