9 Replies Latest reply on May 23, 2013 9:22 AM by Brian Smith

    Help Please - conditional format of colums

    Srinivasa Bezwada

      Greetings everyone,

       

      I am trying to colour code the "Prev Week Var"  column in attached twbx as Red or Green depending on the negative or positive value. If i apply a calculated field "all columns" are being coloured. Can anyone please point me to a way in which I can only colour the values in that column. Thank You.

       

      The Variance values are hard coded for this pilot and not a Actual - Budget value.

        • 1. Re: Help Please - conditional format of colums
          Brian Smith

          got it.

          drag measure values into the color.

          edit colors to custom diverging

          check stepped color = 2 steps (red on left green on left)

          check advanced.

          select start , end, and Center is 0.

          when i edited yours start shows -977 to 977 on the color bar however in the advanced settings start is -0.192982456 end is 977 and center still at 0.

           

          hope this helps.

           

          it still will probably put the prev week and etc. in red if the values are negative but if you do the exact same thing on just Prev Week Var in the color it should work.

          also fyi i pulled "publication" pill down in Rows leaving Measure names only in columns...not that it mattered. but just fyi.

          • 2. Re: Help Please - conditional format of colums
            Srinivasa Bezwada

            Brian,

             

            Thanks a lot for making time and helping out.. Much appreciated. The solution worked and is close, but am wondering if there is a way to keep the non Variance text  like prev week, budget, as black instead of green?

             

            Have a nice day !

             

            SB

            • 3. Re: Help Please - conditional format of colums
              Brian Smith

              SB, No problem.

               

               

               

               

               

               

               

              Just click on the green color box in the edit colors for your custom diverging and make it black.. i would think. sorry pc shut down unexpectedly and I hadn't saved the copy of the wkbk I altered for you.

              • 4. Re: Help Please - conditional format of colums
                Richard Turner

                Hello Brian i have a similar issue.

                 

                i have a table with multiple columns going across. Within this table i have calculated columns such as "% difference 2013-2012", which i created. The problem i am having is i cant seem to conditionally format (by color) those percentages that are <100% and >100%. can you advise?

                 

                thanks

                Richard

                • 5. Re: Help Please - conditional format of colums
                  Brian Smith

                  Richard, Can you post a sample wkbk. I'd have to look at it but think I know a way depending on how many columns you have....

                  • 6. Re: Help Please - conditional format of colums
                    Richard Turner

                    hi there are approx ten columns and i want to highlight three of them. the columns i want to highlight represent a % diff between 2013 and 2012. This column was created by a calculation. i have tried to run some basic if/then statements but for some reason these actions either dont work or highlight the whole table.

                    • 7. Re: Help Please - conditional format of colums
                      Brian Smith

                      So trend. ok if there are no negative numbers in the preceding data which is populating the trend % diff then try this... place measure values in color shelf. select custom diverging (i used red and black in his sample workbook above), stepped color, 2 steps, select advanced and check your start end and center options. set start at the lowest, end at the highest and then center at 100%/1.0/ 100 or whatever the equiv is that populates. However if you do have other numbers that are below that cut off for other measures they sill also likely go red too. That usually never bothers me but just personal preference. Another option would be to duplicate the sheet then do a quick table calc for the trends. and show only the trends then apply same as above. I've had to do that before and I just show the tables side by side in dashboard.

                      • 8. Re: Help Please - conditional format of colums
                        Richard Turner

                        thanks Brian, the issue is, that this logic seems to highlight fields across the whole table (each column) not JUST the % diff column.

                        • 9. Re: Help Please - conditional format of colums
                          Brian Smith

                          you could also try to toggle w/ the band size and level of column banding if you want to highlight the whole column.

                          it depends where you set your center and how you have your pills organized to get it to do what I think your talking about. Without an example it is hard to explain. You may just want to do like I said w/ two wkshts in a dashboard side by side. 1 w/ actuals and 2 with trends% diff etc.