cancel
Showing results for
Did you mean:
Frequent Visitor

## Adjusting Averages by a fixed percentage

I am trying to get the averages of specific models with a Min and Max.  The average is then adjusted for a 25% error removing any data that is above the 25% average or below the 25% average.  The example below is for the PC the average of all 3 is 100.6 with a min of 78 and a max of 134 When i take out the data with the average higher than the 25% mark the new average is 84 with a min of 78 and max of 90. What formulas can i do to:

1. Get the average of of just the PC, DC, or EC

2.Use the 25% varience  of the average to remove data 25% above and 25% below the average

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support Team

## Re: Adjusting Averages by a fixed percentage

You may refer to the measure below.

```Measure =
VAR a =
AVERAGE ( Table1[Sales] )
RETURN
AVERAGEX (
FILTER ( Table1, ABS ( Table1[Sales] / a - 1 ) <= 0.25 ),
Table1[Sales]
)
```
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Super User

## Re: Adjusting Averages by a fixed percentage

You could create a new  column in your table:

```Table1[Adjusted_Sales] =
VAR _Average=CALCULATE(AVERAGE(Table1[Sales]);ALLEXCEPT(Table1;Table1[Models]))
RETURN
IF(Table1[Sales]>_Average*1,25 || Table1[Sales]<_Average/1,25;   BLANK();   Table1[Sales])```

The new column will have only the values that are within 0,75 and 1,25 of the average (per model). You can then place than column in a matrix for instance to extract min, max, average, etc.

Frequent Visitor

## Re: Adjusting Averages by a fixed percentage

Thanks AIB

I am trying to use measure with the actual data

VAR _AVERAGE=CALCULATE(AVERAGE('ASSETS '[Sales USD]), ALLEXCEPT('ASSETS ','ASSETS '[ Models]))
RETURN
IF('ASSETS '[Sales USD])>=_Average*1.25 ||'ASSETS '[Sales USD]<=_Average/1.25
BLANK ()
'ASSETS '[Sales USD])

I am new at power BI, but everything looks fine to me but it keeps telling me "ERROR"

"the syntax for 'BLANK' is incorrect. (DAX(VAR _AVERAGE=CALCULATE(AVERAGE('ASSETS '[Sales USD]), ALLEXCEPT('ASSETS ','ASSETS '[Models]))RETURNIF('ASSETS'[Sales USD])>=_Average*1.25 ||'ASSETS '[Sales USD]<=_Average/1.25 BLANK () 'ASSETS BI'[Sales USD]))).

Super User

## Re: Adjusting Averages by a fixed percentage

@jsiman

This is a column, not a measure.

It doesn't look like you are copying exactly what I wrote previously. Try it like this:

```ADJUSTED_Sales =
VAR _Average =
CALCULATE (
AVERAGE ( 'ASSETS '[Sales USD] ),
ALLEXCEPT ( 'ASSETS ', 'ASSETS '[ Models] )
)
RETURN
IF (
'ASSETS '[Sales USD]  >= _Average * 1.25 || 'ASSETS '[Sales USD] <= _Average / 1.25,
BLANK (),
'ASSETS '[Sales USD]
)```
Highlighted
Community Support Team

## Re: Adjusting Averages by a fixed percentage

You may refer to the measure below.

```Measure =
VAR a =
AVERAGE ( Table1[Sales] )
RETURN
AVERAGEX (
FILTER ( Table1, ABS ( Table1[Sales] / a - 1 ) <= 0.25 ),
Table1[Sales]
)
```
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 416 members 3,720 guests
Recent signins: