8 Replies Latest reply on Mar 26, 2015 4:53 AM by Jonathan Drummey

    Table that shows MIN MAX and AVG for several measures

    Alex Money

      Hello folks, I am trying to create a text table that shows the average, min and max values (in adjacent columns) for a number of measures in my dataset. Is there a quick way of doing this? It sounds simple, but I can't think how to do it. Thanks so much. Alex

        • 1. Re: Table that shows MIN MAX and AVG for several measures
          KK Molugu



          Used superstore data and is this what you are looking for;


          Hope this helps



          1 of 1 people found this helpful
          • 2. Re: Table that shows MIN MAX and AVG for several measures
            Alex Money

            Thanks Karunaker for responding. This is indeed the way I have been proceeding but the issue is that I have c. 30 measures, and I want to show MIN MAX and AVG values for each on a single table. As I understand, this approach would require my replicating each measure 3 times, and then creating the table as you propose.


            This is doable but a bit painful. In excel creating a table like this takes a few seconds. Was hoping there might be a simple way?



            • 3. Re: Table that shows MIN MAX and AVG for several measures
              Jonathan Drummey

              There are things that are easy to do in Excel that are harder to do in Tableau and vice versa. Sometimes when we're trying to make Excel do those harder tasks or making Tableau do those harder tasks it's worth taking a step back and looking at the big picture and the goal to see if that's really the most efficient use of our time and the software.


              It seems like what you're wanting to do is create a text table that is 90 columns wide and and unknown number of rows tall. There are only two things text tables are good for - looking up exact values, and setting up a data dump for another application. If you've got to do a data dump for another application then there's not much to do about that (except maybe find out what that application is doing and see if it can be done in Tableau), however if it's for looking up exact values there may be a couple of alternatives:


              - One is that you haven't specified what the 30 measures are, if they are all members of the same higher level categorization (like months of a year, departments in a company, etc.) then it might be worth reshaping the data so all the measures are really a single column and all those months/departments/etc. are in a second column. Then the view could be created in about 7 clicks, plus one more for each discrete on Rows. For more about reshaping, see Installing the Tableau Add-In for Reshaping Data in Excel | Tableau Software.

              - The second alternative comes from the fact that I've seen many Excel users who are new to Tableau try to duplicate text & conditionally formatted tables in Tableau (I did, myself) and it can be painful because Tableau doesn't approach data in the same way, and to make matters worse they (we) aren't taking advantage of what Tableau can do. For example, very often a text table hasn't really been created to enable lookup of exact values, but was built in that way because that's what people have been used to because of the capabilities of the software they started with. The most common case I see is that the true goal of the table is to "identify top and/or bottom performers". In that situation, instead of showing everything and making the user look it up, why not do the work for them by sorting the view based on the chosen measure (or measures)? The second most common case is some sort of outlier identification, and for that a text table and it's cousin the highlight table just aren't as good as other views like bar charts & line charts, plus maybe doing things like writing some sort of variance calculation (like a % diff from average) and showing that.

              Therefore, I suggest you take a little time to identify what the true goal of this sheet is, and if possible try to take better advantage of what Tableau can do.



              • 4. Re: Table that shows MIN MAX and AVG for several measures
                Alex Money

                Hello. Sorry to reply belatedly - was off. Appreciate the fulsome reply but confess to being older and no wiser as a result of it.


                I want to create a text table that is 4 columns wide, not 90. The column titles would be the Measure Name, Min, Max, Average.


                The table would be 30 rows tall (excluding headers). These are the 30 aggregate measures that I mentioned. They are measures like sales, cost and profit. Each measure is an aggregate of multiple individual forecasts. They cannot be recategorised or amalgamated in any meaningful way.


                So the table would be 30 x 4. I would like to do this on one worksheet. I will be using filters to manage the data (ie specifying the financial year etc.), but this is straightforward to do.


                I am not converting from Excel, but rather have collected this data directly via a web application we have built, and stored it on MySQL. All that stuff works a dream with Tableau.


                Does what I'm trying to do sound overtly complicated?? It seems a pretty basic analysis. Can't help but feel I'm missing something.

                Thanks to all for their input though. It has been interesting and helpful.


                • 5. Re: Table that shows MIN MAX and AVG for several measures
                  Jonathan Drummey

                  Hi Alex,


                  I don't think you're missing anything, the difficulty here is that Tableau has a built-in set of logic around how it visualizes data. When our data is in the zone of what fits in that logic to use your words, "All that stuff works a dream with Tableau.". When our data is not in the zone, then either we need to do some gymnastics in Tableau or reshape our data, or both, and Tableau doesn't give us the directive or prescriptive feedback that we need to take a next step.


                  [An aside - Defining what that "butter zone" is so people can have an easier time in Tableau has become the major focus of the first part of my book, answering forum questions like yours helps me refine my thoughts. I welcome any questions you have about what I've written here.]


                  One way of initially conceptualizing this situation is that you want to take a bunch of columns of your data (the Sales, Profit, etc.) measures and pivot them to appear on rows. Tableau has a built-in mechanism for doing this, namely the Measure Names/Values dimension/measure combination. Here's a snapshot of the raw data as it exists in the database from Superstore Sales:


                  2014-12-17 11_21_03-Tableau - simple scaffold.png

                  And here's the Measure Names/Measure Values "unpivot", which enables us to treat those column names as a dimension and then display the value of each measure in the chosen aggregation:


                  2014-12-17 11_21_55-Tableau - simple scaffold.png


                  However, your goal isn't to end here it's to actually have a crosstab with a set of columns for each of those measures that has the AVG, MIN, MAX, etc.


                  So one way to describe what you're looking for is that the goal is to have measures defining the rows of the crosstab, and additional measures defining the columns of the crosstab, what I think of as a "measure by measure" table. And Tableau doesn't have a built-in way to let us get there from the data as it exists.


                  There are multiple workarounds, however:


                  - A "completely in Tableau" solution is to use multiple worksheets on a dashboard. Each worksheet can be a Measure Names/Values table showing the right aggregation, as in this view (you can see details in the Measure by Measure Multiple Worksheets in the attached workbook):


                  2014-12-17 11_36_59-Tableau - simple scaffold.png


                  The two potential issues with this solution are that if you have more rows than will fit in the vertical display you're going to end up with lots of scrollbars, the other is a potential performance issue because Tableau is rendering N worksheets.


                  - Another workaround is to do that first "unpivot" yourself in the data source, so that way you have the 30 columns of metrics pivoted to be records in the data source, and then you can use Measure Names/Values in Tableau to do the SUM/AVG/MIN/MAX for each. @kettan demonstrated this technique in his earlier post using a custom query that does a cross join. This can introduce sizing & performance problems because you'd potentially be multiplying the number of records x30, an alternative is to do some pre-aggregation prior to the the unpivot so you're minimizing the number of records Tableau has to deal with. There can also be complications in this kind of view if your measures have different types of units. ($$, %, #.#, etc.)


                  - A third workaround is what I think of as the "paint by scaffold" approach where we use a scaffold data source that has the necessary dimensionality to generate the desired layout, our original data source as-is, and a set of calculated fields in the scaffold source to link the two and "paint" our data in exactly the right place on the viz. Because data blending requires us to use aggregates (and we can't make the aggregation a variable), the scaffold has to include both the metric and the aggregation. Then Measure Names/Values can be used for the different types of units and Mark Stacking turned off. I set this up in the attached, here's the "M by M Multiple Formats view":


                  2014-12-17 13_06_54-Tableau - simple scaffold.png


                  In this view I set up a dummy Blend Field to link the sources, it's possible for the scaffold source to have more dimensions to enable showing multiple dimensions (such as a view of each more over time), various filtering options, etc. The view could also use formatted strings instead of Tableau's built-in number formatting, there are a variety of ways to set up scaffold sources depending on your requirements.



                  1 of 1 people found this helpful
                  • 6. Re: Table that shows MIN MAX and AVG for several measures
                    Alex Money

                    Thank you Jonathan Drummey and everyone else who has contributed to this. Relative to the options presented, I've used a primitive solution - an example of which attached. The paint by scaffold approach looks lovely. I fear the creation of same will overtax my small and newbie Tableau skills, but perhaps once I get better at it, this is an option.


                    I do think that a  Measure Aggregates Pill or similar could be a simple and relatively straightforward way to do something like this, however. Thanks to kettan for proposing this. It's not that Tableau struggles with delivering single aggregate calculations from worksheets in my format; but rather that multiple measures of aggregates are problematic. Of course, I don't understand the underlying complexity - so what sounds simple to me, evidently is not!


                    This thread has been very informative, thanks guys. Alex

                    • 7. Re: Table that shows MIN MAX and AVG for several measures
                      Alex Money

                      Hi there,


                      Is anyone on this thread beta-testing Tableau 9? If so, does it include anything like the  Measure Aggregates Pill discussed by kettan and Jonathan Drummey herein??  Thanks Alex