Find the nth highest value

Hello,

I have the attached table. My issue is that the calculation of the Maximum Median Age (the rightmost column) doesn’t work the way I would like. The reason for this is as follows: There exists a higher dimension, which encompasses all of the Techs that can be seen in the screenshot. For the first column, the Median Age Companies, EazyBI simply takes the sum of all the values in the column as the value for this overarching dimension, namely 765.50. The second and third column (Max and Min Median Age Companies) are added calculated statistical measures. They correctly identify that the highest value is 61, and the lowest 2. The fourth and last column, the Maximum Median Age, is a self created measure, which, as the name says, determines the maximum value in the first column (the median age column).

What I want is the following:
I want to be able to grab the 61 and the 2 value, so that I can use them for further calculations in other measures. As far as I can see, there are two options for this:

  1. Somehow grab them directly from the corresponding second/third column. Here I would need to know how I can do that, as I can’t seem to figure out the code to access these columns. I can only access the first and last column so far (which is also the reason why I created the last column in the first place, even though it seems redundant)

  2. Somehow adjust the formula which calculates the measurement in the last column. Right now, it looks like this:
    Max([Searchfield].Members,[Measures].[Median Age Companies])
    As described earlier, the problem is that the value that it shows (764.50) is not the one I want. The correct Maximum Median Age is 61. The value it shows now is the sum of all the Median Ages from the first column, which happens because there is an invisible overarching dimension which for some reason just takes this value as its summary of the column.
    To solve this, I think the easiest way would be to adjust the formula in such a way, that it shows the second highest value of the column, which is the desired 61. This way, I could kind of bypass the issue.

I would really appreciate help with at least one of these two options. Thank you in advance!

Hi @Sonya394,

Welcome to the eazyBI community!

You mentioned that the Max Median and Min Median calculations are eazyBI standard calculations. You can view their formulas by clicking on their headers and copying them. After that, you can define new calculated measures that you can reference in the last calculation.

For the last calculation, you consider all Searchfield dimension members, including the “All” member. I recommend adjusting the calculated measure to consider only specific level members. In your case, the formula could look similar to the one below:

Max(
  [Searchfield].[Searchfield].Members,
  [Measures].[Median Age Companies]
)

Or, you can use the Standard calculation “Max” to consider only the dimension members in rows.

Best,
Roberts // support@eazybi.com