4 Replies Latest reply on Jun 22, 2016 2:07 PM by Ivan Young

    Conditionally formatting multiple measures

    Joe Oppelt

      I'm sure I'm missing something here....

       

      I have a crosstab.  Five different measures, all numeric.  I'm using MeasureNames/MeasureValues.

       

      Some of measures need dollar signs for certain Dimensions, and others do not.

       

      In the attached (V9.0 workbook -- if you upgrade it, I don't care.  Just let me know) I want to display a dollar sign in front of Measure="Current' for Metrics = "Amount" and "Yield".  No dollar sign for the other [Metric] values.

       

      You can see that I have a calc that creates a string with a $ in front for those two [Metric] values.


      This is where I am missing something.  I can't get it to display in place of MeasureValue = [Current].  (Or even in addition to it.)  It's a String value, and I can't get it to shove into the MeasureValues shelf.

       

      This is embarrassing.

        • 1. Re: Conditionally formatting multiple measures
          Ivan Young

          Hi Joe,

          So many of your posts have helped me, it's great to have an opportunity to return the favor.  You probably didn't want to remember how to do this because it's a bit of pain in the A$$.  At least my method is, you'll have to create calcs for all your measures. Of course there could be a better way.  I'm  pretty sure you don't need a detailed explanation but below is a brief one.

           

           

          Create a field to determine if the measure uses $ or #'s

           

          Dollars or Numbers:  IF ATTR([Metric])= 'Yield' or ATTR([Metric]) = 'Amount' THEN 'Dollars' ELSE 'Numbers' END

           

          Formatted Amount: if [Dollars or Numbers] = 'Numbers' THEN SUM([Current]) ELSE -1*SUM([Current]) END

           

          Custom Format for Formatted Amount:  #,###; "$"#,###

           

           

           

           

          Regards,
          Ivan

          • 2. Re: Conditionally formatting multiple measures
            Joe Oppelt

            Well THAT is cool.  I never knew that I could put two formats on custom formatting -- one for positive numbers and one for negatives.

             

            I'm confident that we'll never see negative numbers on the three measures I need to do this for ([Amount], [Target], and [Prior]).  An actual negative number in the data would mess this up.

             

            I was supposed to do this as well on the [Difference from Target] measure.  You can see that we will easily get negatives on that one.  I just showed this to the team, and we'll be OK with not formatting this one.

             

            I owe you lunch at TC17.

            • 3. Re: Conditionally formatting multiple measures
              Ivan Young

              Thanks Joe, This doesn't really work with multiple measures using negatives but you can tweak it to work with one by a two stepped color on the original measure.

               

              I don't think it will work in your case.  But if you need to do this sometime with a single measure, I've attached a workbook.

              • 4. Re: Conditionally formatting multiple measures
                Ivan Young

                As fate would have it one of the top discussions yesterday was different colors for different measures which I didn't know was possible, anyway your workbook was a perfect application for me to test out the method.