Adjusting Averages by a fixed percentage

[Anonymous]

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

[v-chuncz-msft · Accepted solution]

@Anonymous,

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

[AlB]

Hi @Anonymous

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. 

[Anonymous]

Thanks AIB

I am trying to use measure with the actual data

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

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

[AlB]

@Anonymous

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