4 Replies Latest reply on Apr 24, 2018 2:01 PM by Jonathan Drummey

    Conditional color formatting for dimension & measure conditions

    parikshit.shinge

      Hi All,

       

      I have a requirement as follows:

       

       

      I have a grid like above.

      My color condition formatting should be like this: IF ID or Name is 'RESTRICTED' then all measures should be BLUE colored. IF ID or Name is not "RESTRICTED" then only 'Sales Growth' and 'Profit Growth' columns should have color condition formatting (Red for -ve & Green for +ve).

      Is this possible in Tableau?

      I could able to implement only one of the two conditions. Either RESTIRCTED logic or Growth logic.

       

      Please note that here I have shown only 4 measures here. In my actual table, there are 15+ columns and horizontal scroll is present.

       

      Any responses would be appreciated.

       

      Regards,

      Parikshit

        • 1. Re: Conditional color formatting for dimension & measure conditions
          Jyothisree Rayagiri

          Please find the workbook attached.

          You can either drop in your Colourcode calculate field and measurenames together on to colour mark and then set up the colours you want.

          Or use a dummy 0 value measure on the columns to have the option of assigning colour to each measure independently.

          • 2. Re: Conditional color formatting for dimension & measure conditions
            parikshit.shinge

            Hi Jyothisree,

             

            Thanks for your reply. This is working only when there is only one growth measure. I have multiple growth measures. Can you please provide a solution for this? I tried converting the color field to table calculation but I'm not able to generate a single color value for each measure cell. Can you please help?

            The alternate option using dummy 0 has its own issues like headers will need to add separately because of incorrect alignment and so. I have horizontal scroll for measures so i'm not able to use separate header sheet also.

             

            Waiting for your response.

             

            PS - I have updated screenshot in my original question.

             

            Regards,

            Parikshit

            • 3. Re: Conditional color formatting for dimension & measure conditions
              parikshit.shinge

              Hi Jonathan Drummey,

               

              Can you please help me with this?

               

              Regards,

              Parikshit

              • 4. Re: Conditional color formatting for dimension & measure conditions
                Jonathan Drummey

                There are two ways to approach this:

                 

                1) transpose/pivot your data so instead of the measures being measure names/values there are columns for the measure name and value. Depending on how your measures are computed this may take additional work.

                 

                Then you'll have a dimension and can do something like:


                IF ATTR([ID]) = 'Restricted' THEN      "Blue"

                ELSE

                     IF ATTR([Measure]) = "Sales Growth" OR ATTR([Measure]) = "Profit Growth" THEN

                          IF SUM([Value]) > 0 THEN

                               "Green"
                          ELSEIF SUM([Value]) < 0 THEN
                               "Red"
                          ELSE
                               "Black"
                          END
                     ELSE
                          "Black"
                     END
                END

                 

                 

                2) Create a data source with a row for every ID & measure combination and use that to build the crosstab, then using a Tableau data blend to add in a secondary source of your raw data on the ID field with a Value calculated field of the form:

                 

                CASE [Measure]

                     WHEN "Sales" THEN SUM([raw source].[Sales])

                     WHEN "Sales Growth" THEN SUM([raw source].[Sales Growth])

                etc...

                 

                Then you can set the colors using a calculated field like the one in #1 above.

                 

                Again there might be complications with this depending on how the measures are computed.

                 

                If this is not able to get you far enough then please post a Tableau packaged workbook with sample data and your work so far.

                 

                Jonathan