Custom Total by Dimension

Question

I have a data source like this:

Department   Employee  City  Class Peroid   Qty1 Qty2 Qty3
----------------------------------------------------------
Dept1        John       c1     1     1st    1    2    3
Dept1        John       c1     1     2nd    11   22   33
Dept1        Mary       c2     1     1st    2    3    4
Dept1        Mary       c2     1     2nd    22   33   44
Dept2        Joe        c1     1     1st    3    4    5
Dept2        Joe        c1     1     2nd    33   44   55
Dept2        Paul       c3     1     1st    4    5    6
Dept2        Paul       c3     1     2nd    44   55   66

In the workbook I'd like to display regular grand totals along with a custom total by city:

                                             Class  /  Period
                                                    1
             Department    Employee  City     1st        2nd
             ---------------------------------------------------
             Dept1         John       c1    1  2  3   11  22  33
             Dept1         Mary       c2    2  3  4   22  33  44
             Dept2         Joe        c1    3  4  5   33  44  55
             Dept2         Paul       c3    4  5  6   44  55  66
Grand Total                                10 14 18  110 154 198
Total by City                         c1    4  6  8   55  66  88
                                      c2    2  3  4   22  33  44
                                      c3    4  5  6   44  55  66 

Is this possible?

Answer 1

You can achieve that , but not exactly as you show in your mock-up .

Tableau's grand total and subtotal work in a different way as you expect it to be.

Here is the workbook created along with your data . Let me know if that works for you .

Link to public workbook .

Answer 2

Yes its possible in oracle or sql server provides builtin functions. you can use rank() & partition functions to achieve this.