cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mluanacruz
Helper I
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
v-jingzhang
Community Support
Community Support

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


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:

mluanacruz_0-1666066488584.png


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

mluanacruz_1-1666066554123.png

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

Michelle

Helpful resources

Announcements
Carousel_PBI_Wave1

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.

Power BI Summit Carousel 2

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!

Thank you 2022 Review

2022 Monthly Feature Releases

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