1 2 Previous Next 26 Replies Latest reply on Mar 26, 2015 9:59 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

          Alex:

           

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

          minmaxavg-133403-kk.PNG

          Hope this helps

           

          ..kk

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

            Is there a quick way of doing this?

            This question gave me the idea to request  Measure Aggregates Pill.

            If something like this is what you meant, then please consider up-voting mentioned idea.

             

            I strongly believe questions help Tableau to better their products, because questions usually reveal "weaknesses" and "shortcomings" in their products. Actually this is one of the main benefits I was thinking about when asking   What's the value of good forum questions? and to whom?

            1 of 1 people found this helpful
            • 3. 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?

               

              Thanks,
              Alex

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

                Thanks Kettan, that idea looks like it might work. Also would be helpful if you could have Measure Values as a field when doing calculated fields, but I see many others have also brought this up. Cheers, Alex

                • 5. Re: Table that shows MIN MAX and AVG for several measures
                  Shawn Wallwork

                  The more I've thought about this idea of your's Kettan, the more I like it!

                  • 6. Re: Table that shows MIN MAX and AVG for several measures
                    kettan

                    I am very glad to hear that and curious to see how many, if any, vote it up tomorrow.

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


                      Jonathan


                       

                      • 8. Re: Table that shows MIN MAX and AVG for several measures
                        kettan

                        might be worth reshaping the data so all the measures are really a single column

                        It would be an exaggeration to say that reshaping the data is "a quick way of doing this".

                         

                        That said, I reshaped the data and wonder if this is an approach for you,  Alex?

                        See how in attached workbook.  I used technique #3 in  The Cross Join Collection:

                         

                        SELECT d.[Fruit]
                        , l.[Measure Name]
                        , IIF(l.[Measure Name] = 'Sales',  d.[Sales],
                          IIF(l.[Measure Name] = 'Cost',   d.[Cost],
                          IIF(l.[Measure Name] = 'Profit', d.[Profit]
                          ))) AS [Measure Value]
                        FROM [data$] d, [lookup$] l
                        WHERE l.[Measure Name] IN ('Sales','Cost','Profit')
                        

                         

                        UNION ALL could be used instead of a cross join.

                        • 9. Re: Table that shows MIN MAX and AVG for several measures
                          kettan

                          Is there a quick way of doing this?

                          I am happy to announce that there is a quick way!

                           

                          1.  Drag all measures you want analyzed to the biggest 'Drop field here' area:

                          thread 153403 Table that shows MIN MAX and AVG for several measures 1.png

                           

                          2.  (a) Select all measures in Measure Values, (b) right click one of them, and (c) and choose Average and amazingly, all measures aggregates are changed to AVG!

                          thread 153403 Table that shows MIN MAX and AVG for several measures 2.png

                           

                          Ps. Thank you,  Shawn, for encouraging feature discovery in Tableau in  Re: Advance Legends 

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

                            Whether this is "a quick way of doing this." depends on your definition of quick. I like to be as efficient as possible in my use of Tableau, so for me it's worth the effort to put some extra work into having the structure of the data be the most suitable for what I want to do.

                             

                            That said, the Tableau data reshaper plugin takes about 3 mouse clicks, and as demoed at TC14, Tableau v9 will make this a matter of 4 mouse clicks. (2 to select the measures, 2 more to open up the context menu and choose whatever the reshape/unpivot command is called). That's pretty quick to me from a UI perspective. The Excel plug-in does process several hundred rows per second, I have no idea how fast the built-in reshape/unpvot will ultimately be, it's totally dynamic.

                             

                            Jonathan

                            • 11. Re: Table that shows MIN MAX and AVG for several measures
                              kettan

                              Jonathan, thanks for your valuable input ... as usual.

                               

                              Whether this is "a quick way of doing this." depends on your definition of quick

                              My definition of "quick" was related to the original question and therefore my imagination of what Alex defined as quick. I agree spending time on having data in a structure fitting for Tableau saves time.

                               

                              as demoed at TC14, Tableau v9 will make this a matter of 4 mouse clicks. (2 to select the measures, 2 more to open up the context menu and choose whatever the reshape/unpivot command is called). That's pretty quick to me from a UI perspective.

                              I look forward to see how much this undoes the need of custom SQL & external tools for data reshaping.

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


                                Alex

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

                                   

                                  Jonathan

                                  1 of 1 people found this helpful
                                  1 2 Previous Next