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

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

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

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

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

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

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

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_kW BattSize_kWh BattInverter_kW LCC LCC BAU 2795.15 1462.58 233.17 14416500 18515600 2068.64 0 0 10668600 12396900 2125.39 0 0 10302100 12316600 3289.94 3904.96 640.41 13599800 20563900 2024.6 366 169.34 9428730 10323100 2220.15 406.44 99.09 9172660 11419300 2218.26 397.69 97.26 9135230 11352300 2019.27 331.39 159.92 9462390 10383700

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

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