If you have worked with Tableau most likely you have run into a situation where you have a graph (most commonly a bar graph) where you want to show the grand total.

So you go to Analysis – Totals – Show Column Grand Totals. Perfect! I have a grand total but now all of a sudden your whole bar graph gets thrown off because the grand total bar is way longer than the rest! In these cases showing the grand total bar isn’t very helpful.

So you go to Analysis – Totals – Show Column Grand Totals. Perfect! I have a grand total but now all of a sudden your whole bar graph gets thrown off because the grand total bar is way longer than the rest! In these cases showing the grand total bar isn’t very helpful.

After a little research I found some ideas on the Tableau forums. I figured out how to create a calculation that would show the bars for my rows but eliminate the grand total bar.

So how did I do it? It was actually quite simple. You can download this example here. In this example I created calculations for the 3 (Number of Records, Sales and Profit) measures I’m showing. Number of Records now has the calculation “Number of Records – Grand Total” as do the other two measures.

- Each calculation looks like this: IF MIN([Region])==MAX([Region]) THEN SUM([Number of Records]) ELSE 0 END.
- Put my calculations onto the columns shelf
- Add in my grand totals (which won’t work at first). Analysis – Totals – Add Column Grand Totals
- Take the original measures (Number of Records, Sales, and Profit) and add them as labels to their respective marks shelves. This is where the grand total values will show.

I think this will really come in handy and I hope you find it helpful too!

After thought: I would like to mention one downside to this solution is that my calculations only works when I’m using Region as my dimension since it’s hard coded.

Hi ,

ReplyDeleteWe can also achieve with the below calculation.As we all aware, SIZE function returns all the rows in a current partition. Given that the Grand total is technically not on the same partition as the details, we can detect how many rows are there which will be always 1. If we only find 1, we can display a zero instead of sales amounts.

If Size () = 1 then 0 else Sum ([Sales]) end

http://www.tableaulearners.com/tableau-timesavers/