Reply
Highlighted
Frequent Visitor
Posts: 3
Registered: ‎12-18-2018
Accepted Solution

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

PBI Help.JPG

 


Accepted Solutions
Community Support Team
Posts: 4,002
Registered: ‎07-09-2016

Re: Adjusting Averages by a fixed percentage

@RyanBoekeloo,

 

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.

View solution in original post


All Replies
AlB Super Contributor
Super Contributor
Posts: 1,166
Registered: ‎11-12-2018

Re: Adjusting Averages by a fixed percentage

Hi @RyanBoekeloo

 

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
Posts: 2
Registered: ‎12-06-2018

Re: Adjusting Averages by a fixed percentage

[ Edited ]

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 Super Contributor
Super Contributor
Posts: 1,166
Registered: ‎11-12-2018

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]
    )
Community Support Team
Posts: 4,002
Registered: ‎07-09-2016

Re: Adjusting Averages by a fixed percentage

@RyanBoekeloo,

 

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.