cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RyanBoekeloo Frequent Visitor
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

PBI Help.JPG

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

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.
4 REPLIES 4
Super User
Super User

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. 

jsiman Frequent Visitor
Frequent Visitor

Re: Adjusting Averages by a fixed percentage

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
Super User
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]
    )
Community Support Team
Community Support Team

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.

Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

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

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

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.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 165 members 1,851 guests
Please welcome our newest community members: