Forum Discussion
Exclude outliers based on numeric paramater
Hi, I am absolute beginner if it comes to Power BI (as my name says I used to use Tableau). I'm trying to create dashboard which exlude outlers based on user selection. User can choose value which he thinks it's too much for him and exclude all values above.
Example below:
| Person | Sales | Region | Paramater |
| a | 12 | APAC | 90 |
| b | 68 | EMEA | 90 |
| c | 45 | EMEA | 90 |
| d | 89 | APAC | 90 |
| e | 189 | AMER | 90 |
| g | 34 | AMER | 90 |
| h | 78 | AMER | 90 |
| i | 7 | AMER | 90 |
| j | 123 | AMER | 90 |
| k | 45 | APAC | 90 |
| l | 78 | APAC | 90 |
| m | 78 | AMER | 90 |
| n | 345 | EMEA | 90 |
| o | 34 | EMEA | 90 |
| p | 100 | EMEA | 90 |
| r | 46 | EMEA | 90 |
| s | 99 | APAC | 90 |
| t | 67 | APAC | 90 |
| u | 88 | EMEA | 90 |
| w | 123 | AMER | 90 |
| x | 100 | EMEA | 90 |
| y | 90 | APAC | 90 |
| z | 76 | APAC | 90 |
I managed to create paramater and selected 90 as an example. I want to create filter which exclude all people with Sales values over selected value (in this case 90). I did it easily for table with people id but I want to implement it to other vizs. E.g. chart with regions only. Example
AMER: 197
APAC: 457
EMEA: 281
Aggregation after people with Sales over 90 were excluded.
Please help, if you help me I will stop living in constant fear for Power BI because my tableau dahboard are mostly based on such paramaters (i need to migrate all of them from Tableau to PowerBI)
Try the following DAX
Measure = CALCULATE(SUMX(FILTER('Table','Table'[Sales]<='Table'[Paramater]),'Table'[Sales]))This is the result you want
Best Regards,
Jayleny
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Try the following DAX
Measure = CALCULATE(SUMX(FILTER('Table','Table'[Sales]<='Table'[Paramater]),'Table'[Sales]))This is the result you want
Best Regards,
Jayleny
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you! it works perfectly!
Thank you again but I have another question. It helped but in my dashboard I have another measures and I want to exclude all persons which exceed outliers (filter them out from all calculations, if possible from whole database). For now it changes Sales>90 to 0 but if I want to have other measures like e.g. hours then I need to create other calculations. I wish to have column like Ex.outliers: trie/false and remove these people from my database. Hope it makes sense
Hi I_miss_tableau Please see if this helps you
