7 Replies Latest reply on Sep 23, 2016 3:26 PM by Scott Smith

    Find the MIN of one measure and return the value of another measure for that entry/rowID

    Joyce McLaren

      I need a calculation to identify the MIN of one measure and then return the value of a different measure for that entry/rowID.  The values will not necessarily be listed in the view, so a table calculation may not work.

      Any suggestions of how to do this?

       

      Thanks!

        • 1. Re: Find the MIN of one measure and return the value of another measure for that entry/rowID
          David Li

          Hi Joyce, could you clarify a bit more, or even better, attach a packaged workbook (with data sources extracted first)?

           

          What purpose does the MIN of the first measure serve? Will it be used in a threshold of some sort?

          • 2. Re: Find the MIN of one measure and return the value of another measure for that entry/rowID
            Scott Smith

            Hi Joyce,

             

            You could use a Level of Detail calculation to find the MIN value for the Dimensions you're looking at, then test that LOD value against your measure value and return your second measure if the row is the lowest. In the case below, all records that are not the min value will be blank.

             

            For Example

            Calculated Field: IF {FIXED [Dim 1] : MIN([Value 1])} = [Value 1] THEN [Value 2] END

            If you want the MIN across the entire dataset, just use IF {MIN([Value 1])} = [Value 1] THEN [Value 2] END

            1 of 1 people found this helpful
            • 3. Re: Find the MIN of one measure and return the value of another measure for that entry/rowID
              Joyce McLaren

              I need to identify the case (the row of my data) that contains the lowest value for the first measure and then report it alongside the corresponding value for another measurement. 

               

              The specific example is that I have a large data set that contains specifications about different types of energy storage installations. Each entry (rowID) includes the kilowatts (measure A) and the kilowatt-hours (measure B).  I need to find the entry with the lowest kilowatt (measure A) value and then show it alongside the corresponding kilowatt-hour (measure B) value.  The values of the two measures are not necessarily related.  You may find the smallest A, but that doesn't mean that entry has the smallest B.

              In other words, I can't just use 2 MIN() calculations because that will find the smallest value of one measure and the smallest value for the other measure, but they won't necessarily be taken from the same row.

               

              Does that make sense?

               

              Thanks!

              • 4. Re: Find the MIN of one measure and return the value of another measure for that entry/rowID
                David Li

                I think Scott's first solution will work well for you as long as you put the dimension(s) that differentiates between energy storage installations into the FIXED part of the LOD.

                • 5. Re: Find the MIN of one measure and return the value of another measure for that entry/rowID
                  Ivan Young

                  Hi,

                  I use a slightly modified version of Scott's solution which is to create a Boolean filter {FIXED [Dim 1] : MIN([Value 1])} = [Value 1] which you set to true.  When the filter is applied you can select any measure or dim associated with the Min Value without creating additional fields.

                  Regards,

                  Ivan

                  • 6. Re: Find the MIN of one measure and return the value of another measure for that entry/rowID
                    Joyce McLaren

                    Thanks to all of you who replied. These solutions worked to solve that problem! 

                    I now have another (very related) calculation that I'm trying to create and I'm getting errors that I can't overcome. (Please tell me if I need to start this in a new thread instead.)

                     

                    My data looks like this, with each row being at an LOD called [Rate]: 

                           

                    PVsize_kWBattSize_kWhBattInverter_kWLCCLCC BAU
                    2795.151462.58233.171441650018515600
                    2068.64001066860012396900
                    2125.39001030210012316600
                    3289.943904.96640.411359980020563900
                    2024.6366169.34942873010323100
                    2220.15406.4499.09917266011419300
                    2218.26397.6997.26913523011352300
                    2019.27331.39159.92946239010383700

                     

                    Using this dataset I have a calculated field called [Expected LCC Savings], which is: (MIN([LCC BAU]) - MIN([LCC]))

                     

                    Now I want a calculated field that finds the MAX[Expected LCC Savings]  and returns the PVsize_kW, BattSize_kWh, and BattInverter_kW (either one at a time or together).

                     

                    But I get the error that I can't mix aggregate and non-aggregate functions and that the argument to MAX is already aggregated and can't be further aggregated.

                     

                    I tried:

                    IF MAX([Expected LCC Savings]) = [Expected LCC Savings] THEN [PV Size kW] END

                    and

                    IF {FIXED [Rate] : MAX([Expected LCC Savings])} = [Expected LCC Savings] THEN [PV Size kW] END

                     

                    I think that Ivan's idea of creating a Boolean out of the [Expected LCC Savings] and then using it as a filter may work somehow...but I can't make it turn out right.

                     

                    Thanks for any help!

                    • 7. Re: Find the MIN of one measure and return the value of another measure for that entry/rowID
                      Scott Smith

                      Since [Expected LCC Savings] is calculated field using the MIN() aggregation, any further calculations using [Expected LCC Savings] also need to be at an aggregated level. In both of your examples, [PV Size kW] is not at an aggregate level and that's causing the error.

                       

                      I would recommend trying:

                      IF MAX({FIXED [Rate] : MAX([Expected LCC Savings])}) = [Expected LCC Savings] THEN MAX([PV Size kW]) END