10 Replies Latest reply on Jan 15, 2014 12:32 PM by Jonathan Drummey

    Top N among multiple measures

    Anuja S Krishnan

      I have a list of measures in my survey data where each measure is a column. The count / N of each of these columns (measures) varies.

       

      1) I want a table that shows the average of each of these measures by dimensions. While doing that I also want to see the count of each of these measure columns under that dimension.

       

      2) When multiple measures are shown, I want to show the top N or the highest count.

       

      I am attaching a .twbx here. Sheet1 is the average of the measures by the dimensions. I would like to show the valid N for each of these columns which will be maximum (or highest) among the measures listed. If you look in Sheet2, I have shown the count for the measures.

       

      What I want do is the following:

       

      a) add a total row to Sheet2 which will not be a SUM but the MAX at each column.

      b) I want to be able to do this (add the TopN) in Sheet1

       

      Any help is greatly appreciated!

        • 1. Re: Top N among multiple measures
          Mark Smith

          Anuja, this sounds straightforward...can you attach a packaged workbook with a few rows of similar data?  Doesn't have to be the real data, but sometimes theres things we don't expect.

          Thanks,

          Mark

          • 2. Re: Top N among multiple measures
            Anuja S Krishnan

            Thanks Mark. I've attached a packaged workbook in my original post.

            • 3. Re: Top N among multiple measures
              Jonathan Drummey

              Hi Anuja,

               

              Can you post a mock-up of the desired output, given this data set? I'm not clear what you want the Top N to be of, the measure or the dimension? And are you wanting 1 output sheet or 2 sheets? And you talk about wanting the Total row for Sheet 2 to not be the SUM but the MAX, however the measures that have been described are COUNT and AVG, so I don't know what you're thinking would be the SUM that would be the MAX.

               

              Jonathan

              • 4. Re: Top N among multiple measures
                Anuja S Krishnan

                IF there is a way to add this row (TOTAL row highlighted in orange) to the Table1 (the average table below) - that is what I am looking for. I was wondering if there is a way to add the count as a dual axis or something.

                 

                 

                If that is not possible, I can calculate the count in a separate table (Table2) and just show the "total" row (hide the remaining rows) and place it next to the Table1. But, the more important thing is that in Table2, I want only the row totals to be based on MAX while the COLUMN total should be a SUM.

                 

                Table 1                                 Dim2
                ABCTotal
                Measure1

                 

                 

                                             average

                Measure2
                Measure3
                Measure4
                Table 2                                    Dim2
                ABCTotal
                Measure1

                                      

                 

                                          count

                SUM
                Measure2SUM
                Measure3SUM
                Measure4SUM
                TOTALMAX(count)MAX(count)MAX(count)MAX(count)
                • 5. Re: Re: Top N among multiple measures
                  Jonathan Drummey

                  In your post there was no row shown as orange, so I'm still not clear. This is a place where providing an worksheet or text file with exactly the computational results you are expecting would be helpful.

                   

                  Based on my understanding, there are several ways to go about this that I can think of. You could use two worksheets on a dashboard, UNION the data to get two copies, one for the detail rows and one for the measures, or use Tableau's built-in grand totals with some custom calcs.

                   

                  With the first or second solutions, you could potentially "unpivot" your data inside Tableau using calculated fields that would reference each measure, for me the more flexible solution is to make the measures their own dimension, so did the following:

                   

                  1. Copied the data to Excel.

                  2. Used the Tableau data reshaper Add-In to unpivot the data, creating a Measure dimension and a Value measure.

                  3. Brought that data source into Tableau.

                  4a. For the "version 1 count" worksheet, I used the MIN/MAX technique outlined in http://drawingwithnumbers.artisart.org/customizing-grand-totals-part-2. This gets a COUNT(Value) into the Grand Total row, and I did a custom format of the Grand Total name for that.

                  4b. For the "version 2 count" worksheet, I used a variation on that and the table calc technique from the same post to generate a custom grand total that is the WINDOW_MAX(COUNT(Value)). The Compute using of the calc is the Measure (copy), so it partitions on each.

                   

                  Hope this helps!

                   

                  Jonathan

                  • 6. Re: Re: Top N among multiple measures
                    Anuja S Krishnan

                    Sorry I was not clear, apologies! I will try and add things to an Excel in future.

                     

                    "version 2 count" is exactly what I need! I see the average of each measure by dimensions. Finally the last row "MAX Count" is the maximum (highest) count of all the measures under each dimension. I am still trying to figure out how you did it. I am going through the post you've mentioned above. I haven't been able to understand and recreate. I will continue trying.

                     

                    Thanks

                    Anu

                    • 7. Re: Re: Top N among multiple measures
                      Anuja S Krishnan

                      It worked!! THANKS A TON!

                       

                      Two things:

                       

                      1) Is it possible to change the number format on the total row alone? Since these are # of people, I want to keep it at zero decimal. I was able to change the number format by going to Pane/Grand Totals but that also changes the number format of the "Grand Total" column". I want to change the format for the "Max Count" row only.

                       

                      2) I further want to shade the numbers based on conditions for which I have added a calculated item. Only, when I use "Square" option from the Marks drop down menu it disturbs the table by adding some unwanted "..." etc. So, is there a limitation to coloring and formatting in such a table? I added a calculated item that to color numbers less than 4 in amber and greater than 4 in green.

                       

                      Regardless, I think what you've suggested is an excellent solution. Thanks a lot for working with unclear information too

                      • 8. Re: Top N among multiple measures
                        Jonathan Drummey

                        I'm glad it worked!

                         

                        1) There's only one Grand Total format for both rows and columns. There are

                        a couple of workarounds that I can think of:

                         

                        • One is to have separate measures, one would return Null values in the

                        detail rows and a Grand Total, the other would return Null values in the

                        Grand total and values for the detail rows, then you could put both on the

                        Text Shelf with independent number formatting. In the Format Text box where

                        you can edit the text, you'd have to delete the line feed that Tableau

                        returns.

                        • The other is to use Tableau's custom number formatting. You could set up

                        the measure to return a negative number for the Grand Total, then change

                        the number format for negative numbers to not show the sign and not have

                        any decimal places.

                         

                        2) I'm pretty sure you'll need to use Analysis->Stack Marks->Off, see

                        http://drawingwithnumbers.artisart.org/customizing-grand-totals-in-tableau-v8-the-stacking-snag/for

                        some more explanation of what's going on.

                         

                         

                         

                         

                        On Tue, Dec 10, 2013 at 6:01 AM, Anuja S Krishnan <

                        • 9. Re: Re: Top N among multiple measures
                          Anuja S Krishnan

                          (1) Still getting this to work

                           

                          (2) Turning "off" stack marks worked like a charm. Thank you However, I realized I don't need the coloring for the row total where we have done a total of count. I want to add the coloring for the averages only. Should I just create two worksheets and place them next to each other in a dashboard? Would that be the best way to do this or can I control coloring in this table (I am using your table you attached as a sample above)

                          • 10. Re: Re: Top N among multiple measures
                            Jonathan Drummey

                            Hi Anuja, did you get this figured out?