Filtered data in graph

[wmtan01]

Hi!

I'm very new to Power BI and managing our reports. Long story short, we have data tables that contains metrics. Since the readings can be erratic we remove the outliers before using the data. I was wondering if there is a way to do it inside Power BI. what we do now is we manipulate the database which is the datasource by tagging the rows with outliers. The problem with this is that the outliers for each metric does not always appear on the same row (we're measuring performance of pages)

So what I wanted to know is if there's a way for us to import all 120 rows in power BI but for the graphs to exclude the bottom 10 and top 10 values of each metric?

1.2	1.1	0.9
1.4	2.5	2.1
0.7	2.6	3.7
2.9	3.6	2.2

for example in this table, use only (1.2 and 1.4) for the first metric, use only (2.5 and 2.6) in the 2nd metric and (2.1 and 2.2) for the 3rd metric

[Anonymous]

Hi @wmtan01 ,

Not very clear, can you please share more detail information to help us clarify your scenario?

If you wan to calculate the average, please use AVERAGE().

Best Regards,
Eyelyn Qin

[wmtan01]

Hi,

What we needed was to remove outliers on each column and not have it affect the other columns. Looking around, Trimmed Mean was the one we've decided to use, it allowed us to remove a certain % of data that are highest and lowest values then average the remaining ones. Before we were doing normal Average but before we pulled the data to PowerBI we were removing rows and rows of data with outliers. I was looking for a way to minimize the manual steps we were doing.

[MFelix]

Hi @wmtan01 ,

This is possible using a TOPN approach that you can pick up the values based on the sorting you need, or using a ranking and only consider ranking between certain value so in your case the values between the ranking 11 and 109.

However the information you give is very small, so I cannot give you the correct example to fit your model, but If you can give more details maybe I can give you a more generic formula that you can use in your several measures.

Other option is on the Query Editor to sort by the values and then remove the TOP 10 rows and the bottom 10 rows so instead of making the treatment on the database you can do it in Power BI.

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

[wmtan01]

Hi,

I did a little bit more of digging around and saw that what I needed was actually showing Trimmed Mean on my graphs. I saw some other post about how to make it using DAX.

Thank you for your time!