Are you using the Measure Names/Values shelf?
If so, to get the veiw that you want you will need to transofrm your data before Tabelau, wither with custom SQL when connecting or preprocessing your data.
What you want is each measure on its own row in the data source, so if your data looks like:
CustID,Measure1,Measure2,Measure3 A,1,2,3 B,4,5,6 C,7,8,9
You will want to reshape it so it is like:
CustID,Measure,Value A,Measure1,1 A,Measure2,2 A,Measure3,3 B,Measure1,4 B,Measure2,5 B,Measure3,6 C,Measure1,7 C,Measure2,8 C,Measure3,9
Then when you create your calculated field to set the conditional formatting, you can add a requirement that
[Measure]="Measure1"or any other logic. This taller structure give Tableau more flexibility in what you can do.
See http://www.tableausoftware.com/support/knowledge-base/preparing-excel-files-analysis for more details.
I have a problem in conditional formatting. I have a list of items and i have their details like sales, margin, households reach, unit price, etc. in separate columns. I want to highlight the top 10% and bottom 10% values of each column separately.
Yeah, that might be a solution if it was a one off job done primarely for the coloring - however this was meant as a nice visualisation and user aid - not the primary result.
I were looking for something less disruptive to the data warehouse ;-). More like if using the Superstore Sales sample. Adding customers to the rows and the measures Discount, Sales and Profit to Measure Value shelf and Measure names to the Columns.
Then color discounts below 2% green and discounts above 5% red. All other measure values should not be colored.
I prefer to do it in tableau because it will be easier for the user to change values and to implement it in other areas e.g. budget deviation.
However we might be able to implement it in the cube with either conditional formating (If Tableau understand formating comming fra the cube) or just adding a new measure with the valeus 0, 1, 2 to use on the color shelf for color grouping.
You can transform your data for Tableau into the structure that I recommend with a custom SQL statement when connecting to the data source, no need to change data structure in your DW if that is an issue.
If i do it the way you suggested then i wont be able to apply a filter on profit or sales or shipping cost seperately after I publish it. I am highlighting the top 10% but if I want to just filter for all those customers which show a profit of, say, less than $1,200 then i cant do it.
If you can preprocess or use custom SQL, you can get your data in any shape you want. For example, using the example above, a structure of:
CustID,Measure,Value,Measure1,Measure2,Measure3 A,Measure1,1,1,2,3 A,Measure2,2,1,2,3 A,Measure3,3,1,2,3 B,Measure1,4,4,5,6 B,Measure2,5,4,5,6 B,Measure3,6,4,5,6 C,Measure1,7,7,8,9 C,Measure2,8,7,8,9 C,Measure3,9,7,8,9
while not "pretty", does allow for nearly any kind of filtering and analysis Tableau can offer, you could say it is the best of both tall and wide.
or you can use calculated fields to make a tall dataset wide, or you could use data blending to get the same effect as well. you have lots of options when your data is tall.
Thanks for your suggestions - I value you taking the time to answer questions.
The hoops that one would have to jump through in order to get this to work is not really worth it. I love using Tableau for data explorations and for all kinds of visualisations - this was just one that we couldn't crack in a nice way.
I may try to implement something in a cube or suggest it as a feature request
Thanks! But I have one more complication
If I put my data in this form -
the problem will be if the variable measure3 changes to say measure4 and we have one more variable measure5. And then suppose i create an excel file the same way as above. I am connecting to this excel file live and the number of measures is not constant. So I have to build a vertical structure only so that if i put the variable 'Measure' on the columns shelf all values in it will appear as different columns.
So the problem now becomes -
1. I have to do conditional formatting for each column separately
2. I have to be able to filter for some values within each Measure after publishing
3. The number of values in the Measure column is not constant (so i think only a vertical structure will work)
I am struck with this for the last 4 days. Please help!
How about if you have a data structure like:
and then use calculated fields to add in the other columns for filtering, like:
and when you filter, filter on SUM() of that calculated field, and ensure the dimension "CustID" is on a shelf in the worksheet.
If you you can provide an example of what you are are starting with, and what you want your end result to be, I am sure a solution can be found.
This appears to be a common use case and should be supported without having to reshape data. Most "measures" naturally sit as columns in files and tables and not in the unpivoted form. Perhaps one way to achieve this is for Tableau to provide -
a. access to the view's "metadata" using which I can write conditional expressions based on a row / column / measure / dim name (there are plenty of other scenarios where this could be helpful) OR
b. control of the marks / colors etc by column / row and eventually cell
I'd almost prefer (a) so as to not clutter the existing interface which works beautifully for the 8 out of 10 things that I want. But I can see it getting tricky (due to nested shelves etc) and hard to design for the edge-cases.