3 Replies Latest reply on Apr 10, 2018 2:44 AM by Vimal Annamalai

    How to compare & calculate Percentage difference of two worksheets values?

    Vimal Annamalai

      HI All,

       

      I have below two Worksheets ( I have attached the sample workbook):-

      1) B1_AvgIOPS

      2) B2_AvgIOPS

       

      My requirements are:-

      1) I need to calculate the percentage difference of  above 2 worksheets values(Like below screenshot).

      2) No Problem if the percentage difference is in New Worksheet also.

      3) Need to color code if Percentage value > 0 =  green  & If percentage value < 0 =  yellow &  If percentage value < -1 =  red.

       

       

      Kindly help me out, I have been struggling for few days. Your help will be appreciated.

        • 1. Re: How to compare & calculate Percentage difference of two worksheets values?
          Joe Oppelt

          You'll have to have all the values on one sheet.  We don't pass values like that between sheets.

           

          I notice that you use single-select filters (or "All") on the filters that are specific to the two sheets, and that can be to your advantage.  If you were to make parameters instead of filters, you could have both sets of selections function within the same sheet.

           

          In the attached I created a parameter called [Build2 Parameter].  I loaded it from [Build] so it contains all the values.  Then I added one more value "All".  Next I created a calc [Avg IOPS B2] that grabs [Avg IOPS] values based on the selection from the parameter.  You could use that new calc on your B1 sheet, and then you would be able to do your math in sheet 1.  (You would need to do the same for [TestDate2], [TestDate1] and [Build 1].

           

          See attached.

          • 2. Re: How to compare & calculate Percentage difference of two worksheets values?
            Mike Mainzer

            Vimal, why do you have these in two different sheets? They're both coming from the same data source, correct?

             

            Instead of creating two different sheets with different filters, you need to create two calculated fields and specify your filters in the calculated field.

             

            This way you'll have two measures that can be displayed in the same sheet. Then create a third calculated field that is the percent difference between your first two. Again, all in the same sheet.

            • 3. Re: How to compare & calculate Percentage difference of two worksheets values?
              Vimal Annamalai

              Hi Mike Mainzer,

               

              Yes, you are correct. I'm happy If i get it in this way.

               

              Instead of creating two different sheets with different filters, you need to create two calculated fields and specify your filters in the calculated field.   -- > how to do this, Could you please help me?