Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
mluanacruz
Helper I
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:

mluanacruz_0-1666239306315.png

 

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

mluanacruz_3-1666239601358.png

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
amitchandak
Super User
Super User

@mluanacruz ,

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

Hi @amitchandak ,

 

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

mluanacruz_0-1666734104315.png

Can you please advise how to proceed?

 

Thanks,
Michelle

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors