cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Filter rows based on average of unrelated column

Hello,

I want to filter rows in table A based on the average of an unrelated column in table B. Both tables are unrelated.
Basically, the logic of filtering should be-
(average of column in table B) <= filtered rows >= (average of column in table B + 10000)

Kindly help. Thank you.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion
Community Champion

Re: Filter rows based on average of unrelated column

@Zabeer 

 

SampleTable1

Val
5
6
5
8
9
2
4
1
2

 

SampleTable2

Val
1
2
3
4
5
6
7

 

Sample Filter measure

Filter = 
var _avg= AVERAGE(SampleTable2[Val])
var _selectedVal= SELECTEDVALUE(SampleTable1[Val])

var _result= IF(_selectedVal>=_avg&& _selectedVal<=_avg+2,"Yes","No")

//you have to replace 2 with required value. 
return _result

 

Apply this measure as a visual level filter of the 1st table.

 

00.JPG

 



Did I answer your question? Mark my post as a solution!
Appreciate with a kudos
🙂

View solution in original post

2 REPLIES 2
Highlighted
Community Champion
Community Champion

Re: Filter rows based on average of unrelated column

@Zabeer 

 

SampleTable1

Val
5
6
5
8
9
2
4
1
2

 

SampleTable2

Val
1
2
3
4
5
6
7

 

Sample Filter measure

Filter = 
var _avg= AVERAGE(SampleTable2[Val])
var _selectedVal= SELECTEDVALUE(SampleTable1[Val])

var _result= IF(_selectedVal>=_avg&& _selectedVal<=_avg+2,"Yes","No")

//you have to replace 2 with required value. 
return _result

 

Apply this measure as a visual level filter of the 1st table.

 

00.JPG

 



Did I answer your question? Mark my post as a solution!
Appreciate with a kudos
🙂

View solution in original post

Highlighted
Anonymous
Not applicable

Re: Filter rows based on average of unrelated column

Is this not dead easy to do in Power Query?

Best
D

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.