5 Replies Latest reply on Sep 21, 2018 7:35 PM by Joe Oppelt

    Change Background color and custom font color of measure values

    Sara Kim

      Hello - I am able to change background color of the measure values, but I also need to custom change the font colors based on criteria.

       

      For example - I need to change the font color for Sales to be in RED if sales > 100 And same thing for Profit if Profit > 100 then that column's font color to be in red.

       

      How can I do both change background color and custom font color of certain columns based on the criteria? Please note - don't want to change the font of entire row - but just that column it self.

       

      I am attaching the sample workbook. Thanks in advanced!

       

        • 1. Re: Change Background color and custom font color of measure values
          Sara Kim

          Checking if can anyone help with this please?

           

          Pinging Joe Oppelt  Thanks!

          • 2. Re: Change Background color and custom font color of measure values
            Joe Oppelt

            You're not going to be able to use MeasureName/MeasureValue like you have on sheet 1.  Tableau will handle the formatting of the text uniformly across all the measures.

             

            On Sheet 2 I changed the display (only two of the measures for now) to a technique that lets you format each measure column separately.  (Note:  This involves some tedious work to get things just right.)

             

            I put a bogus measure on the columns shelf for each measure you want to display.  Here I used SUM(0).  Then for each mark I put the actual measure on the TEXT shelf (such as sum of profits.)  This creates an axis at the bottom of each, with 0 as the axis label and a 0 axis tick.  For the Profits column I edited the axis label to say Profits, and I set ticks to NONE.  You'll do this for all your measures.

             

            On Sheet 3 I changed things further.  I changed the bogus measure to be a constant, and I changed the mark type from square to bar.  This allows for narrower rows, and because there is a constant value (AVG(something)) rather than a SUM of 0, I get a full, wide background color.

             

            Next I started messing with the colors for the text.  Look at what is in the text shelf for the AVG(2) mark.  I have two calcs.  One I can set to red, and the other to black.  The logic in the calcs is such that I will either have one or the other set, and whichever is set with a value, the other will contain null.  I placed them side-by-side in the label editor, and colored one red and the other black.  For kicks I also changed the font size of red.  You can see the results.  Only one will display in each row because the other will be null.  (If I had put a calc on the color shelf, it would have controlled the color of the bar, not the color of the text.

             

            I didn't do any formatting of AVG(10).  I think you just needed to see how to make it work, and now you can apply this to your actual application.

             

            I'll stop here for now.  I could discuss a whole bunch of other formatting details you can play with, (and some limitations this technique has), but you may or may not need them.  So if you want to change something and can't figure out how to get to it, just ask.

            • 3. Re: Change Background color and custom font color of measure values
              Sara Kim

              Hi Joe,

               

              Thank  you so much for your help! I will try this and let you know with questions.

               

              One quick thing I noticed was - I need all of the headers on the top like Profit, Sales along with dimension headers. Right now I see them on the bottom. How can we do that?

               

              Thanks

               

              • 4. Re: Change Background color and custom font color of measure values
                Joe Oppelt

                Yes, I can get the labels to the top, but it's another tedious layer, and I can't get to that now.


                I'll show you later this weekend.

                • 5. Re: Change Background color and custom font color of measure values
                  Joe Oppelt

                  In the attached I made a copy of the last sheet.

                   

                  To get labels at the top, make a dual axis with yet another bogus measure.  When you do that, you get a label at both the top and the bottom.  Clear out the axis label off the bottom and set the top one to Profits.  You'll have to format the new bogus measure to get the zero-line out of there, and color the zero bars the same color as the original profits bars.

                   

                  So yes, if you are doing a dozen measures like this, you'll have a dozen dual axis pairs.