cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Create a custom filter for a table based on metric

Hi,

I've got a table that looks kind of like this:

Product IDViews
1500
2300
3450
430
520

 

What I want to have in my report is a table that gives me all the products IDs, that are over the average of views, into which only values over 100 are counted

 

So in the end, I want this:

Product ID
1
2

 

because the average of the values above 100 is 417 and these two are the only ones above.

 

I hope I made my problem clear, I'm aware of the fact that it seems really specific Smiley LOL

 

 

Thanks,

Leon

1 ACCEPTED SOLUTION

Accepted Solutions
chetanamare Regular Visitor
Regular Visitor

Re: Create a custom filter for a table based on metric

Hi @Anonymous ,

 

Create a measure to determine the Average views above 100.

 

Average Views Above 100 = CALCULATE(AVERAGE(MyTable[Views]),FILTER(ALL(MyTable),MyTable[Views] > 100))
 
Then create a measure to determine whether the View value in a row is greater than the measure created above:
 
View Greater Than Average = IF(MAX(MyTable[Views]) > [Average Views Above 100],1,0)
 
Then add the measure 'View Greater Than Average' as a Visual level filter to the table and check for the value '1'.
You should get the desired results.
 
Regards,
Chetan
 

View solution in original post

5 REPLIES 5
NickNg278 Senior Member
Senior Member

Re: Create a custom filter for a table based on metric

@Anonymous,

Easiest way for you to do is dragging Views value to your visual level filter, and filter views greater than 100. See pictures below

test.PNG

 

Community Support Team
Community Support Team

Re: Create a custom filter for a table based on metric

@Anonymous ,

 


I've got a table that looks kind of like this:
Product ID Views
1 500
2 300
3 450
4 30
5 20

 

What I want to have in my report is a table that gives me all the products IDs, that are over the average of views, into which only values over 100 are counted

 

So in the end, I want this:

Product ID
1
2

 

because the average of the values above 100 is 417 and these two are the only ones above.

 

I hope I made my problem clear, I'm aware of the fact that it seems really specific Smiley LOL

 

 

Thanks,

Leon


Why Product ID 3 is not in the count column? Could you share more details about the logic?

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Re: Create a custom filter for a table based on metric

@v-yuta-msft yes I'm sorry, very dumb to make a mistake when you want to explain something difficult.. What the output should actually look like is this: 

 

Product ID
1
3

 

because the average of all the products that have over 100 views is 417, therefore Product 2 wouldn't be in the list, but Product 3 would. Hope you understand better now.

 

Thanks,

Leon

chetanamare Regular Visitor
Regular Visitor

Re: Create a custom filter for a table based on metric

Hi @Anonymous ,

 

Create a measure to determine the Average views above 100.

 

Average Views Above 100 = CALCULATE(AVERAGE(MyTable[Views]),FILTER(ALL(MyTable),MyTable[Views] > 100))
 
Then create a measure to determine whether the View value in a row is greater than the measure created above:
 
View Greater Than Average = IF(MAX(MyTable[Views]) > [Average Views Above 100],1,0)
 
Then add the measure 'View Greater Than Average' as a Visual level filter to the table and check for the value '1'.
You should get the desired results.
 
Regards,
Chetan
 

View solution in original post

Anonymous
Not applicable

Re: Create a custom filter for a table based on metric

That worked perfectly.

 

Thank you guys for helping!

 

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)