cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
procyon82
Resolver I
Resolver I

Removing Outliers

Is there an easy way to remove any outliers in Power BI desktop?

I am currently importing revenue from Google Analytics, but every now and then we get very large purchases that scew the averages. Could I simply apply a filter and say "avg. revenue only on the values that are under $5000"? Or maybe use a standard deviation function that we'll do this automatically for me?

2 ACCEPTED SOLUTIONS
jahida
Impactful Individual
Impactful Individual

That reads as: In some context (CALCULATE), is the SUM > 5000?

 

I think you want:

 

Revenue Month  = CALCULATE(SUM('GA Ecommerce'[Revenue]), 'GA Ecommerce[Revenue] < 5000,PARALLELPERIOD('GA Ecommerce'[Date],0,MONTH))

View solution in original post

Hi @procyon82,

 

Now what you need is removing outliers at daily level instead of individual transactions level, right?

 

If that is the case, you can add a new table to sum up the revenue at daily level by using SUMMRIZE function. I have tested it on my local environment, here is the sample expression for you reference.

DailyRevene = SUMMARIZE(Daily,Daily[Date],"Daily total",SUM(Daily[Sales]))

Capture.PNGCapture1.PNG

 

Then you can remove the outliers on daily level in this new created table.

 

Regards,

Charlie Liao

View solution in original post

8 REPLIES 8
Greg_Deckler
Super User
Super User

In the Query Editor, make sure that the column is numeric and then click on the drop down arrow in the column header and choose "Number Filters" and there you have a choice of:

 

Equals

Does not equal

Greater than

Greater than or equal to

Less than

Less than or equal to

Between


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

I guess the only downside with this is that it filters the entire query and all the columns/measures that I have. Is there a way to only apply it to a column or measure?

Yes, for that you would use a CALCULATE function (DAX). Basically:

 

CALCULATE(SUM([Some Column]),[Some Column] < 500000)

@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

Any idea why the response in a table is "True" or "False" instead of a number?

 

I'm using: 

Revenue Month  = CALCULATE(SUM('GA Ecommerce'[Revenue]) < 5000,PARALLELPERIOD('GA Ecommerce'[Date],0,MONTH))

 

jahida
Impactful Individual
Impactful Individual

That reads as: In some context (CALCULATE), is the SUM > 5000?

 

I think you want:

 

Revenue Month  = CALCULATE(SUM('GA Ecommerce'[Revenue]), 'GA Ecommerce[Revenue] < 5000,PARALLELPERIOD('GA Ecommerce'[Date],0,MONTH))

 

Got it to work. I am not sure what I was doing wrong earlier, but I started from scratch.

The only issue right now is that it's looking at daily revenue and not individual transactions, so I just gotta figure that out.

Hi @procyon82,

 

Now what you need is removing outliers at daily level instead of individual transactions level, right?

 

If that is the case, you can add a new table to sum up the revenue at daily level by using SUMMRIZE function. I have tested it on my local environment, here is the sample expression for you reference.

DailyRevene = SUMMARIZE(Daily,Daily[Date],"Daily total",SUM(Daily[Sales]))

Capture.PNGCapture1.PNG

 

Then you can remove the outliers on daily level in this new created table.

 

Regards,

Charlie Liao

No, I wasn't looking for outliers by looking at daily totals. I was looking for individual transactions that would skew my averages.

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!