Pick group totals, grand totals and normal summarization in Tableau

Question

I am trying to calculate average of 2 columns in another column in Tableau but difficult part is grand total is not getting average instead it is the sum of 3rd calculated field.

  A    B   Calculated field

  10   5    2
  6    3    2
T 16   8    4 (Here I should get 2 instead it is taking sum of column)

Here I am unable to write separate formula for row totals and grand totals, Only one formula (Calculated Field) is allowed and when I am dragging on sheet it is by default aggregating to sum.

Note: I am expert in Crystal and BO but beginner in Tableau.

Update

Code used for LoD

{FIXED [Product Category]: AVG([Sales])}

Below image is what I got after implementation I have tried with 2 columns but the result is same if I use only one column (I am trying to get the average of sales)

Jil Jung Juk Jil Jung Juk · Accepted Answer · 2017-09-28T11:40:28

You are almost there - the Grand Total by default does a SUM function you just have use the Total All Using --> Average option.

Output : Level wise SUM(Profit) later averaged across columns and rows. (Show Column Grand Total & Show Row Grand Total active)

Update: Answering the question below. To get the Row-wise avg (which is Cat1-vag in this case) you could just drop the measure and change it to AVG(). Since you needed in a Calculated Field you could use a Simple FIXED LOD. You can also uncheck aggregated measures from Analysis dropdown and have no Dimension in column or row like unlike what this example shows and still get three different averages. Cheers.

{FIXED [Cat1]:AVG([Profit])}

Pick group totals, grand totals and normal summarization in Tableau

Update

2 Answers