11 Replies Latest reply on Feb 14, 2012 5:54 PM by Prashant Balepur

    Conditional formating of measures

    Peter Thomsen

      I have a problem conditional formatting of measures

       

      The scenario:

       

                                    Measure 1 || Measure2 || Measure3

      Cust1

      Cust2

      Cust3

      Cust4

       

      I would like to only format Measure1. It could be by grouping the values in Measure1 into groups eg. Low, Middle, High and all other measures into NA.

      However I cannot find any way to either scope my calculated field or to use the "Measure Names" dimension.

       

      Does any have an idea?

       

      Best regards

      Peter

        • 1. Re: Conditional formating of measures
          Joe Mako

          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.

          • 2. Re: Conditional formating of measures
            Rohan Dhanashri

            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.

            • 3. Re: Conditional formating of measures
              Joe Mako

              here is an example workbook.

               

              With the altered data structure, all sorts of interesting things are possible.

               

              I've also attached the Excel file for before and after views.

              • 4. Re: Conditional formating of measures
                Peter Thomsen

                Hi Joe

                 

                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.

                 

                //Peter

                • 5. Re: Conditional formating of measures
                  Joe Mako

                  Peter,

                   

                  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.

                  • 6. Re: Conditional formating of measures
                    Rohan Dhanashri

                    Joe,

                     

                    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.

                    • 7. Re: Conditional formating of measures
                      Joe Mako

                      Rohan,

                       

                      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.

                      • 8. Re: Conditional formating of measures
                        Peter Thomsen

                        Joe

                         

                        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

                        //Peter

                        • 9. Re: Conditional formating of measures
                          Rohan Dhanashri

                          Joe

                          Thanks! But I have one more complication

                          If I put my data in this form -

                           

                          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

                           

                          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!

                          • 10. Re: Conditional formating of measures
                            Joe Mako

                            How about if you have a data structure like:

                             

                            CustID,Measure,Value

                             

                            and then use calculated fields to add in the other columns for filtering, like:

                             

                             

                            IIF([Measure]="Measure1",[Value],0)


                             

                            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.

                            • 11. Re: Conditional formating of measures
                              Prashant Balepur

                              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.