Taking Super Stores as an Example my requirement is the following :
Requirement is that the Sub Total of the Sub Category should always show the Respective Categories Full Total (Irrespective of the no. of Sub Categories filtered out)
Putting Category and Sub Category in Rows and Showing Sales in Columns we get Sheet 1.
Now , if Furnishings is Filtered out of Sub Category (as done is Sheet 2) , the sub total of Furniture still remains the same.
This is the easy part. and as you can see has been already done.
However the issue comes when we filter out all Sub Categories except one.
In such a case I am not able to get the Full Category Total as Sub Category Total.
See Sheet 3.
How can we ensure that even if One Sub Category is selected the Sub Total always shows the full Category Total
Would prefer not to use lookup(attr(.....),0) and windows calcs.
Jonathan Drummey SMMC
Chandra Shekhar JainCEO - Vayaa Systems
e:csj@vayaasystems.com ; m:+91 800 802 3344
Hello Chandra Shekhar Jain,
I've played with this a bit and maybe null or someone else has a better idea but I found Jonathan's post about using Custom SQL to provide the only manageable soluton for this issue if you need the sub-total to appear in the same column as the Sub-Category values. It's fairly straight forward to just use your existing calculation: sum({ FIXED [Category]:sum([Sales])}) as a new column and call it "Sub-Total" - i have done this on Sheet 3.
On Sheet 4, I have used the method mentioned by Jonathan - using Custom SQL - to create a view that provides the desired values. The downside of this approach is a value appearing in the filters as "Sub-Total". Note that the field [Copy] is on the rows shelf and has had the header turned off. With this in the view, we get the nice break between the Categories and Sub-Total.
I hope that gives some ideas for you and maybe others have thought up a solution with LOD calcs which will work with a single select. I think the problem is the same regardless that trying to address the total and sub-total lines directly will be lost when there is just a single row in the view.
I created this example with Tableau 9.2. If you are not currently running the latest version, it can be downloaded here: Alternate Downloads Site | Tableau Software
Patrick
