cancel
Showing results for
Did you mean:
Helper I

## Average excluding High and Low

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!

2 REPLIES 2
Community Support

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

Helper I

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*

Michelle

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### 2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors