Group calculation in Tableau

Question

I have some data where I would like to create a query that will perform 2 separate calculations based on the group id in tableau

Here is my data

id  total   avail
A   10  5
A   40  20
B   20  10
B   10  5
B   10  5
A   20  10
A   20  10
B   10  5
B   10  5

I wish to group all the 'A' ids and sum them (Group1) and I wish to group all the 'B' ids and Average them. (Group2)

This is what I am doing:

first group the IDs:

IF [Id] = 'A' THEN 'Group1'
ELSEIF [Id] = 'B' THEN 'Group2'
ELSE 'none'
END

Now perform calculation:

IF ATTR([group]) = 'Group1' THEN  SUM('A') ELSEIF
ATTR([group]) = 'Group2' THEN  SUM(['B'])/SUM(['B']) ELSE 0
END

However, this is not correct. I am still researching this. Any suggestion is appreciated.

Answer 1

One suggestion here- Create groups in Tableau (by right clicking the field instead of having these through calculations).

I am giving solution on the basis on previous sampled data.

data sample used-

+--------------+-----+-----------+--------+------+
| total_backup | sku | available | date   | used |
+--------------+-----+-----------+--------+------+
|           10 | A   |         5 | Sep-19 |    5 |
+--------------+-----+-----------+--------+------+
|           20 | B   |         5 | Sep-19 |   15 |
+--------------+-----+-----------+--------+------+
|           30 | B   |        10 | Sep-19 |   20 |
+--------------+-----+-----------+--------+------+
|           10 | C   |         5 | Sep-19 |    5 |
+--------------+-----+-----------+--------+------+
|           10 | C   |         5 | Sep-19 |    5 |
+--------------+-----+-----------+--------+------+
|           10 | Z   |         1 | Sep-19 |    9 |
+--------------+-----+-----------+--------+------+
|           10 | Z   |         1 | Sep-19 |    9 |
+--------------+-----+-----------+--------+------+
|           20 | Y   |        10 | Sep-19 |   10 |
+--------------+-----+-----------+--------+------+
|           30 | Y   |        10 | Sep-19 |   20 |
+--------------+-----+-----------+--------+------+
|           40 | X   |        10 | Sep-19 |   30 |
+--------------+-----+-----------+--------+------+
|           40 | A   |        15 | Sep-20 |   25 |
+--------------+-----+-----------+--------+------+
|           20 | A   |        10 | Sep-20 |   10 |
+--------------+-----+-----------+--------+------+
|           10 | B   |         5 | Sep-20 |    5 |
+--------------+-----+-----------+--------+------+
|           40 | C   |        15 | Sep-20 |   25 |
+--------------+-----+-----------+--------+------+
|           50 | C   |        15 | Sep-20 |   35 |
+--------------+-----+-----------+--------+------+
|           10 | Z   |         5 | Sep-20 |    5 |
+--------------+-----+-----------+--------+------+
|           10 | Z   |         2 | Sep-20 |    8 |
+--------------+-----+-----------+--------+------+
|           20 | Y   |         5 | Sep-20 |   15 |
+--------------+-----+-----------+--------+------+
|           10 | X   |         2 | Sep-20 |    8 |
+--------------+-----+-----------+--------+------+

make a new calculated field desired new val as

zn(sum(if [Sku (group)]= 'sum group' then [val] end))
+
zn(avg(if [Sku (group)] = 'avg group' then [val] end))

last step build your view as

P.S. for adding tooltip you need not keep 'total' column. It can be added anyways.