Is this what you want? I created a new sheet that shows subtotals and implements a table calculation filter on the category field.
This filter will be applied after the total is calculated so you can show/hide categories without affecting the department total.
The formula I used is:
Category Filter = lookup(attr([Category]),0)
You can use max() or min() instead of ATTR(), but you need an aggregate in order for this to work.
Note this solution could be slow if your query is returning too many marks. The actual total calculation is being done in Tableau and not in the source.
Let me know if this helps.
ParentTotal.twbx 1.5 MB
Great.. it is exactly what i am looking for. Thanks for your time.
one quick question, can we get it as the first column and then can we have the child dimensions.
also, how can we get it in the source it self. any idea on how we could modify the sql.
Could you clarify what you mean by ...
can we get it as the first column and then can we have the child dimensions.
how can we get it in the source it self. any idea on how we could modify the sql?
If you are asking whether you can add the calculation to the SQL, the answer is yes, but I think that would just complicate things.
I'll be able to give you more specifics after I understand your questions better.
In the attached workbook, we have the total as the last column.
what i trying to ask is.. if we can get this as the first column before category.
hopefully, i was clear. please let me know if you have any questions.
ParentTotal_solution.twbx 1.5 MB
I don't know of a way to move the location of the totals in Tableau. Have you tried searching the forums for this?
If you have access to the database, you could write a view that has a "summary level" column. This column would indicate if the record represents a category level or a department level summary. You could then rearrange the different values of the "category" to show totals to the left.
If your database supports this syntax, you can use GROUP BY Department, ROLLUP(Category). This will calculate a summary record for each category/department combination and one for each department (all categories).
Alternatively, you can create a couple of GROUP BY queries and UNION them to pre-calculate all the summaries you need:
SELECT 'Category' as summary_level, department, category, sum(Sales) as Sales
GROUP BY 1,2,3
SELECT 'Department' as summary_level, department, 'All' as category, sum(Sales) as Sales
GROUP BY 1,2,3
Although you can do this with custom SQL from Tableau, I find the performance is better if you use a DB table or view. Tableau seems to want to run the custom SQL every time you change something on the worksheet.
I am sorry I can't give you an easier answer. Maybe other people have other ideas.
I have added functionality to this solution now.
I have multiple sheets which uses this Category Filter. Category Filter = lookup(attr([Category]),0)'
and on a dashboard i have all these sheets. am i not able to make the above filter global. any idea, why ?
Also, when we do show quick filter for Category Filter, we get an extra '*' value.
any idea why is it so?
I believe the table calculation fields are local to the view and I don't think there is a way to make a global filter using a table calculation. Let me know if this is a big deal and we can try to ping someone else to come look at this thread and hopefully offer some suggestions.
The * shows because we are using ATTR(). You can search this forum for attr() and learn more about it.
To avoid the *, you can use max() or min() instead of attr() in the calculated field formula.
thank you, the * is not visible now.
What i am trying to do is a number of charts with parent and child metrics and then combine them on a dashboard. i want both the parent level trend and the corresponding child level trends with out compromising on the parent level metrics, meaning parent level metrics should always remain the same irrespective of the child selections.
the method that you gave perfectly solves this problem. now, it is required for me to extend it to other charts which are on the dashboard.
it is required for me.. can you please check to see if there is any way we can extend it on a dashboard.
Jonathan Drummey any ideas to make a table calc filter global? The only option that comes to mind here is calculating the totals in the DB and using a regular filter like I mentioned in one of my earlier messages.
Only dimension & regular aggregate filters can be scoped or global filters. And only dimensions can be the origin & target of Filter Actions. The only way I know of to make table calc filters “global” is to use a parameter and then make table calc filters in each worksheet responsive to the parameter.
However, there’s an alternative route that might work in this case. Use a duplicated data source, and turn off any linking dimension(s) that you want to filter in the primary but not the secondary. Then you can use the filtered measure from the primary and the non-filtered measure from the secondary. I don’t have a workbook handy nor time to put one together right now, hopefully that makes sense!