Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Remove spikes from data

 

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!

 

graph.png

 
 
 
1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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.

@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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Thank you @Greg_Deckler 

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.