5 Replies Latest reply on Mar 22, 2016 8:58 AM by Pablo Saenz de Tejada

    Minimum and % difference from Minimum

    Arihant Jain

      Hello,

       

      In the attached sheet, I want to identify the minimum value out of all the 3 measure and % difference in each of the measure from the minimum value.

       

      Thanks for the help.

      Arihant Jain

        • 1. Re: Minimum and % difference from Minimum
          Pablo Saenz de Tejada

          Hi Arihant,

           

          something like this will work?

           

          In this case I have created a table with the MIN price w/o Freight per Product, the Average, and then the % Difference between the average and the MIN per product. Is something like this what you need?

           

          Regards,

          Pablo

          • 2. Re: Minimum and % difference from Minimum
            Arihant Jain

            Thanks Pablo.

             

            But I want to show one single value. If you see the Dashboard, I have applied couple of filters, out of which I want to show the Minimum value only and % difference of those value from minimum value.

             

            Hope you have understood, how I want to present it.

             

            Regards,

            AJ

            • 3. Re: Minimum and % difference from Minimum
              Pablo Saenz de Tejada

              Hi Arihant,

               

              I think I understand. The idea will be quite similar in any case. If you do for example: MIN([Price w/o weight)] and add that to the Measure Values, it will give you the minimum value for that measure. And creating a calculated field like (AVG([Price w/o Freight])-MIN([Price w/o Freight])) / MIN([Price w/o Freight]) that will give you the difference as a % between the average value and the minimum value for that row.

               

              The reason using the average is that if you use the SUM, you'll be always summing the records of that measure, that will always be higher than the minimum and doesn't make sense, while it does the average. That works if you want to calculate the minimum and difference for each row against all values in that same row of data.

               

              If you want to compare the rows between them, then you will need to calculate the WINDOW_MIN of each measure and compute it using Table Down. So if you create for instance the WINDOW_MIN(SUM([Price - as per Canon Product's List])), put it in measure values, right click on the measure and select Compute using > Table Down. That will give you the minimum of that value for all rows.

              Then you'll have to create the % difference of each row against that window min, like this:

               

              (SUM([Price - as per Canon Product's List]) - WINDOW_MIN(SUM([Price - as per Canon Product's List]))) / WINDOW_MIN(SUM([Price - as per Canon Product's List]))

               

              Again compute using Table Down, that should give you the % difference of that measure to the minimum in all rows. Then you can do the same calcs for the other fields. Maybe is this what you were looking for?

               

              Regards,

              Pablo

              • 4. Re: Minimum and % difference from Minimum
                Arihant Jain

                Hello Pablo,

                 

                It doesn't give me correct input. I want the minimum of (Price as per canon), (price w/o freight) and (price with Freight).

                 

                As per the below screen shot minimum value should be "3.1"

                 

                 

                Thanks for the help.

                AJ

                • 5. Re: Minimum and % difference from Minimum
                  Pablo Saenz de Tejada

                  Hi Arihant,

                   

                  Ok, and can you have in a same row a price for "Price w/o freight" and a "Price with Freight"? If you always have 1 of the 3 values, you could first create a calculated field to give you the concrete value.

                   

                  Like:

                   

                  IF SUM([Price 1]) >0 THEN SUM([Price 1]) ELSEIF SUM([Price 2]) >0 THEN SUM([Price 2]) ELSE SUM([Price 3]).

                   

                  This will return price 1, 2 or 3 (the one there's a value for). So the MIN([New calculation]) will be 3.1 in that example. And then you can use the minimum and lookup for that new calculated field and get the % difference correctly.