Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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?
Solved! Go to 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))
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
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
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)
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))
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]))
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
112 | |
108 | |
102 | |
94 | |
71 |
User | Count |
---|---|
173 | |
134 | |
132 | |
102 | |
95 |