Unfortunately, what you're talking about sounds like adding two unique Grand Totals to the same view - one set to Sum, the other set to Average. This really isn't possible - Tableau will do a calculation on the group, it can use Grand Totals to do a single calculation on all of the data.
But here's I think where you'll start to find the true benefits and meaning of Tableau's flexibility and ease of use. Some things, like this, aren't clearly do-able out of the box, but there's almost always a way (or two, or more) to do what you want to do with just some tweaks and manipulation.
So with that said, here's an example that does what you want, using two views combined in a dashboard. See the attached - sheet "Overall View". Others may have alternate solutions, but this is a pretty simple one:
I first started by recreating most of what you have with one of the sample data sets, in sheet "Overall With Totals".
Then I duplicated the view, set all of the calculations to be Averages, instead of Sums (sheet "Averages - Part 1").
Now it's all about formatting. I removed the Customer Segment field from the rows, and you will just have the overall average - once you remove Grand Totals. (As an aside, this is one thing you'll find Tableau does very very well, which is that you will never find it doing bad math by default, like taking averages of averages.)
Now to format nicely, I'd want to put a label where I previously had Company. I created a field called [_Average Label] here, which is simply set to be the text "Average". Then I dragged this to the Rows.
Then, for final formatting purposes, I removed the column headers (drop down bow under Measure Names, or right-click on the column headings, and un-check "Show Header"). Then on the label title, hide this by right-clicking and checking "Hide Field Labels for Rows". You can see how this looks in sheet "Averages - Final Formatted".
Final step is combining these in one dashboard ("Overall View"). I lined up the columns, and it should continually line up. As a tip, you can also use things like a Blank piece (in your Dashboard design on the left side of the page, under Objects) to fill in spaces or move things around if you add more and more.
This also works really well if you wanted to always show the totals, but had a long list of values you needed to scroll through. See the sheets labeled "Alternate __" - same concept as before.
Hope this helps!
P.S. As an FYI, even though your data sources are confidential, it's quick and easy to use the built-in Tableau data sources (Sales data and Coffee Shop data). I even find that often in re-creating my issue for the forums, I find the solution myself!
TotalsAvgs.twbx.zip 1.2 MB
I've got a lot to chew on here, but I'll give it a go. Tell me, after I get it all together, would I still be able to key on and use the Average values to "stoplight format" the measures (above avg/avg/below avg), as shown, below?
MeasureA MeasureB MeasureC 1 14 3 2 8 12 3 1 9 Grand Total 6 23 24 Average 2 8 8
Will also review the sample data, as you suggested.
Yes absolutely. The ONLY issue with the first chart you wanted to see, was displaying two "Grand Totals" in the same sheet (the Grand Total and the Average, which is another Total type). Otherwise it's completely fine.
For the coloring, I made a quick new workbook with some options.
There is one very simple way to do what you're looking for, and that is to bring in Measure Values onto the Color option in your Marks tab. You'll notice when you do that, it will apply the same scale to all of your numbers, and it will treat it as a range, rather than as discrete.
If you edit the Light Green / Dark Green option that is the default and use the "Temperature" color scheme, it'll be pretty close to the traffic light colors you are looking for. You can use this same thing to put a background in place, if that's easier. Both of those I have in this new workbook.
But if you want a lot of complexity, like if Measure A has different high/med/low criteria the Measure B, or anything like that, there are very cool ways to do it. If you want to get really complex with it, I put in another sample marked "Complex Coloring".
(This entire method was taken fromJonathan Drummey's fantastic walkthrough of pretty much every way to color a simple text chart. It's great to download and work through, and does a really in-depth job of explaining how to do everything: http://public.tableausoftware.com/views/conditionalformattingv4/CrosstabofManyColors , including step by step how I did the Complex Coloring slide.)
At the end of it all, I'm still using the trick of combining every piece I want on a single dashboard that I started with.
TotalsAvgs.twbx.zip 1.1 MB
Thanks, Catherine. Great info here.
I think (one of) the problems I'm running into is the fact that I'm working with an OLAP cube. After duplicating my worksheet and changing the Measure Values to "Total Using: Average," my Grand Total line shows the averages, as expected.
However, when I remove the Customer Segment dimension from the Rows shelf, the Total reverts to the sum Grand Total, and not the average. I was able to go the other way, though, so that I had a _GrandTotal line to use in the dashboard. Not as clean as I had hoped, but it works, so thank you!
With regard to complex coloring, each Measure column would have its own red/yellow/green criteria, with respect to the average value we've created in the Grand Total row. For discussion purposes, let's ditch the thought of having both Grand Total and GT Avg on the same worksheet, so that my Grand Total row just shows the average of all the Customer Segment Measure Values column data (Customer Segment is still on the row shelf). So, if the Grand Total Avg for the Measure A column was $181, then I need the Measure A value for each Customer Segment to be green in that column if > $181, yellow if = $181, and red if < $181, which was what I was trying to convey in my second post. Sorry if I wasn't clear.
Unfortunately, I'm having to cut my teeth on multi-dimensional data, which from what I gather creates issues all its own...