You might be able to do this with some format trickery and format that part of the totals to white where it does not show. I will try this on a mock workbook.
I could not find any format option that targeted just that section of the totals
I've a solution for you. In order to understand how it works it might require some understanding of table calculations to a slightly greater level than point and click,
We have built two calculations that are table calculations. They simply check if FIRST() = LAST()
One of these basis it on running across the table and the other is based on partitioning by 1 field and addressing the other two.
In table calculations scope and direction matter. In this regard although the calculations look the same they are looking at a different set of fields. For instance a running total might run across the table or it might run down the table. So in this regard we can check multiple conditions.
In our case we first find out if something is the subttotal or grandtotal. If it is then we return the sum(sales) if it is not then we check the next condition. The next condition is whether or not it is the first() AND the last() based on each row. Because totals start the table calculations over the totals areboth first and last. If they are first and last we exclude them and we show anything that is not first and last (based on the row) as the sum of sales.
There is most likely a more succinct solution for this but table calcs are my friend and they can be your friend too.
TableCaclulationMagic.twbx 1.2 MB
Thank you SOOOO MUCH! The example you posted is exactly what I wanted and I was able to apply it to my workbook successfully!!
Unfortunately I don't completely understand what I (or rather you haha) did and why it works!
Are there any discussion threads or reference articles or training videos that explain it in more detail?
1 of 1 people found this helpful
Here are some informative blog posts that you might find helpful. I'd venture that only a very small fraction of Tableau users abuse table calculations in this manner in order to achieve the certain results. However because you're curious (that's a good thing) anytime you want a deeper dive you can find it.
I would honestly recommend just playing with them as well. Use FIRST() and put it on your text shelf. Then use LAST() and do the same. Change your scope and direction (the compute using thing). Repeat with INDEX(), etc. Its one of those things that is hard to learn unless you have a challenge that duplicates the need or else its just like vocabulary words. However knowing that FIRST() and LAST() and how they work inside a table and in totals can be fairly valuable.
Nice job Carl!
Thank you John. If only you knew why I know this. (very very very specific
requirements from certain clients). Happy always to impart knowledge and
lessen others pain when able.
On 1 August 2016 at 11:25, John Sobczak <email@example.com>