1 of 1 people found this helpful
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?
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 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
The more I've thought about this idea of your's Kettan, the more I like it!
I am very glad to hear that and curious to see how many, if any, vote it up tomorrow.
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.
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.
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:
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!
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, 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.
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.
1 of 1 people found this helpful
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:
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:
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):
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":
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.