Average excluding High and Low

[mluanacruz]

Hi
I am trying to get the average of a measure excluding the highest and lowest numbers.

AVG x Hi/Lo =

VAR Maximum =

    MAX( [Ratio_Measure] )

VAR Minimum =

    MIN ( [Ratio_Measure] )

RETURN

    CALCULATE (

        [Ratio_Measure] ,

        [Ratio_Measure] <> Maximum

            && [Ratio_Measure] <> Minimum)

But I keep getting this error message: "The MAX function only accepts a column reference as the argument number 1." I guess I can't take a max from a measure?

Here is a sample of my data: HERE!! 

This is my measure :

Ratio_Measure =

VAR DEV_MNT = MAX('PD_agg_perf_measures'[DEV_MONTHS])

VAR NuM = CALCULATE(SUM('PD_agg_perf_measures'[CNP_CNT_ACCOUNTING_VIEW]),FILTER(ALLSELECTED('PD_agg_perf_measures'),[DEV_MONTHS]= DEV_MNT&&[ACCIDENT_DATE_YYYYMM]=MAX('PD_agg_perf_measures'[ACCIDENT_DATE_YYYYMM])))

VAR DEN = CALCULATE(SUM('PD_agg_perf_measures'[CNP_CNT_ACCOUNTING_VIEW]),FILTER(ALLSELECTED('PD_agg_perf_measures'),[DEV_MONTHS]=DEV_MNT-1 &&[ACCIDENT_DATE_YYYYMM]=MAX('PD_agg_perf_measures'[ACCIDENT_DATE_YYYYMM])))

RETURN DIVIDE(NuM, DEN,0)

What is the best way I can do this?

Thanks for the help!

[v-jingzhang]

Hi @mluanacruz 

A measure always returns only one aggregated value in its current context, so you are not able to use MAX or MIN to get the maximum or minimum value from a single value. 

The link of sample data is not valid, can you update the link? In addition, please explain what your Ratio_Measure does. You can share some expected output based on the sample data so that we can understand the result better. 

Best Regards,
Community Support Team _ Jing

[mluanacruz]

hi @v-jingzhang 

Thanks for the help. Here is! the updated link!
When you open the link above you will see the excel sheet with the formulas I am trying to accomplish. The Ratio_Measure is a division between the previous DEV_MONTH/ Most current DEV_MONTH:

The expected output will be just like this picture below: * Please see formulas on the excel sheet*

I hope this was helpful! Thanks again for your help.

Michelle