topic Re: Remove spikes from data in Developer

Remove spikes from data

Anonymous — Mon, 01 Jun 2020 08:12:37 GMT

Hello everyone!

To be honest I am not sure what I am asking is even possible in Power BI but I thought I would ask. So I have the graph shown below with some avg runtime data. But you can see that before and after the two main runs (which hopefully you can identify) there are some random spikes that in order to get some valid min/max information, they would have to be removed.

What I thought is, creating some sort of script in Python that removes those spikes or replaces them with 0s but I don't know if thats possible inside Power BI. At the moment, it would be really hard to manipulate the data externally so if you have any ideas, it would be much appreciated. Thank you!

Re: Remove spikes from data

Greg_Deckler — Mon, 01 Jun 2020 12:15:43 GMT

@Anonymous - Perhaps TRIMMEAN? https://community.powerbi.com/t5/Quick-Measures-Gallery/TRIMMEAN/m-p/1074075#M504

Re: Remove spikes from data

Anonymous — Mon, 01 Jun 2020 14:09:46 GMT

Hello @Greg_Deckler and thank you for your answer!

It seems like a really good answer but I am not quite sure of what data it gets. Is there any modification that I can put in that piece of code to show me a graph (like the one I showed) and how it has been trimmed down? I hope I made sense. I am really new to DAX.

Re: Remove spikes from data

Greg_Deckler — Mon, 01 Jun 2020 14:55:17 GMT

@Anonymous - Well, to use that measure, you would replace the highlighted portion with your column name and you would need to replace the 'Table' reference with your table name. You would replace the .2 with the percentage of values that you want to trim off (top/bottom %)

TRIMMEAN =
VAR __Table =
ADDCOLUMNS(
'Table',
"Rank",RANKX('Table',[Value])
)

VAR __Percent = .2

After that, you would use this measure instead of what you are currently using in your visual.

Re: Remove spikes from data

Anonymous — Tue, 02 Jun 2020 09:04:41 GMT

Thank you @Greg_Deckler

That really helped! I got all the data I needed!