cancel
Showing results for
Did you mean:
RyanBoekeloo 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 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:

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.

jsiman 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]))).

Thanks for your time 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:

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]
) 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 Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag. Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform. Top Kudoed Authors
Users Online
Currently online: 165 members 1,851 guests
Recent signins:
• whyjumponmynerv • jessh54321 • vanillaicecream • Fuhrer786 • GarlonYau • data_insights • doyleg_OEH • data_insights • KyleFoster • powerbipresto • SysvisionPBI • asellars1 • jbJB • Hydr0x 