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. I've attached a packaged workbook in my original post.
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.
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 A B C Total Measure1
Measure2 Measure3 Measure4 Table 2 Dim2 A B C Total Measure1
SUM Measure2 SUM Measure3 SUM Measure4 SUM TOTAL MAX(count) MAX(count) MAX(count) MAX(count)
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!
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.
It worked!! THANKS A TON!
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
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
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
some more explanation of what's going on.
On Tue, Dec 10, 2013 at 6:01 AM, 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)
Hi Anuja, did you get this figured out?