cancel
Showing results for
Did you mean:
Helper I

## Average without the highest and lowest number of a measure! Help!

Hi, I am reposting because I need help with this.

I am struggling to find a way to find a measure to calculate the average EXCLUDING THE HIGHEST AND LOWEST numbers from my ratio_measure. Please see the data in excel with the formulas of what I want to accomplish and the BI dashboard here: https://drive.google.com/drive/folders/1tw2kxHBZ93OjckLULVxbonYXQaWjPDld?usp=sharing

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*

What is the best way to accomplish this as MAX and MIN don't accept a measure?

Any help is very much appreciated.

Grateful for this community!

Michelle.

2 REPLIES 2
Super User

measure =

var _max = maxx(values(Table[Period]), [Meausre])

var _min= maxx(values(Table[Period]), [Meausre])

return

if(isinscope(Table[Period]), [Measure], Averagex(filter(Values(Table[Period]), [Measure] <> _min && [Measure] <> _max) , [Measure]))

Measure is the current measure you are using

Helper I

Hi @amitchandak ,

Thanks for the help but I keep getting this error below:

Thanks,
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!

#### Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

#### Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors