How to perform two different aggregation on one measure and further aggregate the results to view as one aggegated measure in Tableau

Question

I have a dataset where I wish to place the data in two groups, based on the value name. I then wish to average the result for one group and sum the result for the other. Finally, I wish to sum these two to create a barchart.

updated Here is the data

    Id  Total   Avail   Date    group   used
     A  10      5   9/1/2020    Group1  5
     A  40      20  9/1/2020    Group1  20
     B  20      10  9/1/2020    Group2  10
     B  10      5   9/1/2020    Group2  5
     B  10      5   9/1/2020    Group2  5
     A  20      10  9/1/2020    Group1  10
     A  20      10  9/1/2020    Group1  10
     B  10      5   9/1/2020    Group2  5
     B  10      5   9/1/2020    Group2  5

This is what I have done

1. First create calc. field named : group - group the data into groups

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

Then create calc. field - sum_avg sum of Group1 (A) columns and take average of Group2 (B)

CASE [group]
WHEN 'Group1' THEN { FIXED [Id]: SUM([Avail])}
WHEN 'Group1' THEN {FIXED [Id]: SUM([used])}
WHEN 'Group1' THEN {FIXED [Id]: SUM([Total])}
WHEN 'Group2' THEN { FIXED [Id]: AVG([Avail])}
WHEN 'Group2' THEN {FIXED [Id]: AVG([used])}
WHEN 'Group2' THEN {FIXED [Id]: AVG([Total])}
END

Here is that result:

Desired result:

I wish to add the Group1(sum) and the Group2(avg) for the Avail, Used and Total so that the final chart combines the two blue values and combines the green values.

The SUM of Group1A Avail = 45 and the AVERAGE of G Avail Group2 = 6
So I wish the Avail section (blue) of the barchart to be: 51


and the Used (green) should be 51 as well
with the total as 102 (Ill add to tooltip)

Any suggestion is appreciated- I am still researching this and any suggestion is appreciated

UPDATE

I have a dataset where I wish to reflect the totals from the custom SQL query. Here is some sample data:

Size Tb   Val       type           Group      Sum_AVG    SKU      Last_refreshed

270       90.5      Free_Space_TB  Group2     90.5       Excel    9/1/2020
270      179.5      Used           Group2     179.5      Excel    9/1/2020

Here is the custom query output

Here is my view

The avail and used appear when I hover over, but how would I include the total?

This is the calculation I am using (thanks to help from a SO member):

{SUM({Fixed [type]: ZN(sum(if [Group]= 'Group1' then [Val] end))})
+
sum({Fixed [type]: zn(avg(if [Group] = 'Group2' then [Val] end))})}

SUM_AVG is:

zn(sum(if [Group]= 'Group1' then [Val] end))
+
zn(avg(if [Group] = 'Group2' then [Val] end))

I am doing something wrong, because it is totaling up across all the columns, when I just want the total for each column.

(Used was created from using a custom query)

user_2884859 · Answer

The following solution is proposed-

The total column is of no use. Please drop it. It will unnecessary increase the size of your data. (for tooltip I'll tell you how to do it).

sample data used

+----+-------+--------+------+
| Id | Avail | group  | used |
+----+-------+--------+------+
| A  | 5     | Group1 | 5    |
+----+-------+--------+------+
| A  | 20    | Group1 | 20   |
+----+-------+--------+------+
| B  | 10    | Group2 | 10   |
+----+-------+--------+------+
| B  | 5     | Group2 | 5    |
+----+-------+--------+------+
| B  | 5     | Group2 | 5    |
+----+-------+--------+------+
| A  | 10    | Group1 | 10   |
+----+-------+--------+------+
| A  | 10    | Group1 | 10   |
+----+-------+--------+------+
| B  | 5     | Group2 | 5    |
+----+-------+--------+------+
| B  | 5     | Group2 | 5    |
+----+-------+--------+------+

pivot two columns (used and avail). A gif is included below

Create a calculated field CF as

zn(sum(if [Group]= 'Group1' then [Val] end))
+
zn(avg(if [Group] = 'Group2' then [Val] end))

Drag AGG(CF1) to rows shelf and to text also, type to colors in marks card; you'll get your desired view with 51 and 51 values in both types used and avail.

for total tooltip i.e. 102 create a calc field total as

{SUM({Fixed [Type]: ZN(sum(if [Group]= 'Group1' then [Val] end))})
+
sum({Fixed [Type]: zn(avg(if [Group] = 'Group2' then [Val] end))})}

add this field to details in marks card (instead of tooltip as you do normally).
click on tooltip and edit calculation there as per taste. I have edited it like

Out of total   TB SKU,  was

You'll get tooltip like this.

and

P.S./EDIT This is regarding pivoting data in tableau. Instead of connecting to complete data through SQL connection, you can modify the sql query by following the instructions mentioned here. Two options-

Option-1 creating used column in sql and pivoting in tableau. While importing data/creating connection use this query-

select ID, date, avail, total - avail AS used, group from table_name

Option-2 pivot the data in sql itself. Use this query then-

select ID, date, avail as val, "avail" as type, group from table_name
UNION
select ID, date, total - avail AS val, "used" as type, group from table_name

thereafter you can proceed in tableau.

See the tableau uses long data format and your data is in wide format. You should keep your SKU memory allocation in rows instead of columns so whenever you'll need a division, adding an extra field type to marks card will do the job. If instead you will have it in columns, these are always two separate measures whereas in actually it is one measure. I suggest you to read some articles on internet about tidy data format, wherein you'll understand what to keep in rows, columns and column_names clearly. Reshaping data to a correct and tidy format solves a lot of problems.

Remember, if there is any value in variable(column) name you have to pivot a data. Here used and avail are variable values and therefore these cannot be in column_names (i.e. variable names) I think I am pretty clear.

Good Luck.

How to perform two different aggregation on one measure and further aggregate the results to view as one aggegated measure in Tableau

1 Answers