Exclude outliers based on numeric paramater

[I_miss_tableau]

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)

[Anonymous · Accepted solution]

Hi  @I_miss_tableau 

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.

[I_miss_tableau]

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

[I_miss_tableau]

Thank you! it works perfectly!

[Chakravarthy]

Hi @I_miss_tableau  Please see if this helps you 

https://www.youtube.com/watch?v=AZAL-QPn5Zc