3 Replies Latest reply on Apr 4, 2012 6:49 AM by Jonathan Drummey

    Help with Coloring Data

    Peter Unger

      I am creating a dashboard for work.  On the left side, I show 20 different divisions within the organization.  On top, going across are 18 questions that we asked each group.  The data or text is the average answer (scale of 1 to 5) for all the employees within that group.  I want to highlighted only the highest and lowest answers for each group.  Again, the data can be read with high to low by question asked or high to low by group.  Either way, how do you isolate the highlighting or coloring to just each column or just each row and not affect the rest of the dashboard.  Thank you.

        • 1. Re: Help with Coloring Data
          Jonathan Drummey

          Hi Peter,

           

          Here's one way to do this. I'm assuming that your questions are all different values of the same Question dimension, and that the answers have been reshaped. If that is not the case, then you'll need to do something different and I'd suggest posting a packaged workbook with some sample data.

           

          1. Create a calculated field like:

           

          IF AVG([Answer]) = WINDOW_MAX(AVG([Answer])) THEN "High Score"

          ELSEIF AVG([Answer]) = WINDOW_MIN(AVG([Answer])) THEN "Low Score"

          ELSE "Other"

          END

           

          2. Put that field on the Color shelf. Then click on the blue pill on the Color shelf, and set your Compute Using to Table (Across) (or Question) to highlight by Group, and Table (Down) (or Group) to highlight by Question. I suggest you use the specific dimensions, that way the results won't be so subject to change if you bring other dimensions into the view.

           

          If you wanted to make this user selectable, you could create two calculations like the above and set their Compute Using approprirately, then create a parameter to choose the dimension, then a calculated field that would return one or the other calc based on the parameter. That latter calculated field would be on the Color shelf. I created an example of this using the Coffee Chain DB in the attached workbook.

           

          Jonathan

          • 2. Re: Help with Coloring Data
            Peter Unger

            Jonathan,

             

            I can not thank you enough.  Your answer was exactly correct.  I was really stuck for several days and could not find the answer anywhere.  You truely saved me on this project and will allow me to really impress my co-workers.  Thank you for making me look good.  Again, thank you so much for the time you spent to reply.

             

            ~ Peter

            • 3. Re: Help with Coloring Data
              Jonathan Drummey

              You're welcome!