Calculate totals within custom SQL query (Tableau)

Question

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

1. I first performed a custom query that was a join, unpivot and placed my data into groups

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
814      701       Free_Space_TB  Group1     701        Gris      8/1/2020
814      112       Used           Group1     112        Gris      8/1/2020

2. Then I aggregated the data by taking the sum of one group and the average of the other group (and final summed these groups values)

The data is being aggregated like this: (SUM_AVG)

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

The view looks like this

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))})}

I am doing something wrong, because it is totaling up across all the column(s), (I have more columns in the full dataset) when I just want the total for each column.

(Used was created from using a custom query)

Any assistance is appreciated.

AnilGoyal AnilGoyal · Accepted Answer · 2020-11-24T16:15:54

In my opinion, this you can do without changing the underlying view. WINDOW_SUM is a table calculation and is always dependent on view/context generated. Therefore, I always prefer LOD calculations which do not depend on context.

I think you should proceed like this. As always I have changed the sample data to include sufficient details

Data used

| Id | Avail | group  | used | Date       |
|----|-------|--------|------|------------|
| A  | 5     | Group1 | 5    | 20-01-2020 |
| A  | 20    | Group1 | 20   | 20-01-2020 |
| B  | 10    | Group2 | 10   | 20-01-2020 |
| B  | 5     | Group2 | 5    | 20-01-2020 |
| B  | 5     | Group2 | 5    | 20-01-2020 |
| A  | 10    | Group1 | 10   | 20-01-2020 |
| A  | 10    | Group1 | 10   | 20-01-2020 |
| B  | 5     | Group2 | 5    | 20-01-2020 |
| B  | 5     | Group2 | 5    | 20-01-2020 |
| A  | 5     | Group1 | 5    | 20-02-2019 |
| A  | 20    | Group1 | 20   | 20-02-2019 |
| B  | 10    | Group2 | 10   | 20-02-2019 |
| B  | 5     | Group2 | 5    | 20-02-2019 |
| B  | 5     | Group2 | 5    | 20-02-2019 |
| A  | 10    | Group1 | 10   | 20-02-2019 |
| A  | 10    | Group1 | 10   | 20-02-2019 |
| B  | 5     | Group2 | 5    | 20-02-2019 |
| B  | 5     | Group2 | 5    | 20-02-2019 |

Step-1 Pivot generated in tableau as earlier.

Step-2 Calculated field sum-avg also generated as discussed.

step-3 View generated

Step-4 Add another field total

{FIXED [Date], [Group]: sum(
{FIXED [Date], [Group], [type]: zn(sum(if [Group]= 'Group1' then [val] end))}
+
{Fixed [Date], [Group], [type]: zn(avg(if [Group] = 'Group2' then [val] end))}
)}

Step-5 Add this field to details on marks card. See the GIF here

the code used in tooltip is mentioned below. Obviously, you can tweak it as per taste.

Under the <Group> ,  <AGG(Sum_Avg)> was <type> out of total <SUM(Total)> SKU on     <YEAR(Date)>

Calculate totals within custom SQL query (Tableau)

2 Answers