Create a custom filter for a table based on metric

[Anonymous]

Hi,

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 

Thanks,

Leon

[v-yuta-msft]

@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 

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]

@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

[Anonymous · Accepted solution]

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

[Anonymous]

That worked perfectly.

Thank you guys for helping!

[Anonymous]

@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