4 Replies Latest reply on Jan 29, 2013 9:27 AM by d.bb

    Averaging Columns


      I'm new to Tableau, and have not had any formal training, so much of the lingo may be lost on me...  I'm finding that some of the seemingly simple things I need to do are not overly apparent, and downright maddening.  Several days spent Googling and searching the forums have turned up tons of things that don't apply, or for whatever reason, are not available in my menu options.  Forgive me, but you may see me as a frequent poster here in the near-term as I pull out the rest of my hair... 


      I'm using Tableau Desktop 7, and am connecting to an OLAP data source.  My data is sensitive, so I can't really post samples.


      I have a worksheet that is simply tabular data, and will stay tabular, most of which is straight from the cube, with no other calculations other than applying various filters.  Measure Values are on the Column "shelf," while Dimensions are on the Rows.  Any calculated measures I've created are also columnar.


      I was able to add a Grand Total horizontally across the bottom of the table, but I also need to add a row below that for showing the average calculated from each column above.  Each average value will be used later as a threshold, to highlight data points in it's column (e.g. above average, below average, "around average"). 


      Potential forum/web solutions found include using SUM (Tableau gives me "unknown function sum"), or use right-click options to duplicate measures, etc. but these are not available in my menus.


      In the simplest terms, I want to be able to do this:


      Grand Total62324


      Surely this is possible?  Thanks in advance for any and all assistance.



        • 1. Re: Averaging Columns
          Catherine Rivier


          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!

          • 2. Re: Averaging Columns

            Thanks, Catherine!


            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?


            Grand Total62324



            Will also review the sample data, as you suggested.


            • 3. Re: Averaging Columns
              Catherine Rivier

              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.

              • 4. Re: Averaging Columns

                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...