Removing Outliers

[procyon82]

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?

[jahida · Accepted solution]

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

[Greg_Deckler]

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

[procyon82]

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?

[Greg_Deckler]

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

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

[procyon82]

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 · Accepted solution]

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

[procyon82]

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.

[v-caliao-msft · Accepted solution]

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

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

Regards,

Charlie Liao

[procyon82]

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