Filter to show data at aggregate level not row level

[newpi]

Hi,

I have a dax measure for total duration. Right now its a filter on a table visual which has user name & Activity & total duration as fileds . So when I Filter the table with saying duration > 30 mins, it filters all users & activity which have duration > 30 mins at row level. What I want to do is, I want to calculate the duration on activity level not activity per user level. 

What I have 

User Name	Activity	duration
U1	meetings	1 hr
U2	meetings	20 mins
U3	non-meetings	35 mins

So when i apply the filter of duration > 30 mins, it shows me U1 & U3, but I want my selection to also show U2, since meetings duration combined for U1 & U2 is > 30 mins.

Also, tabular representation above is a simple way of giving this example, I'm using other visuals on this and not just tabular. So need a solution that would work irrespective of the visual selected.

[amitchandak]

@newpi , You have to try something like

averageX(filter(summarize(Table,Table[User Name],"_1", sum(Table[duration])), [_1]>30),[_1])

[newpi]

@amitchandak This does not seem to work. Also, duration is a measure for me not a column so can't use the sum function and had tried with sumx .

[v-lili6-msft · Accepted solution]

hi @newpi 

You could just create a measure as below and add it into visual level filter then set filter is > 30.

To filter = CALCULATE(SUM('Table'[duration]),FILTER(ALLSELECTED('Table'),'Table'[Activity]=MAX('Table'[Activity])))

and here is sample pbix file, please try it.

by the way, you could also use ALL instead of ALLSELECTED in this measure.

Regards,

Lin

[Greg_Deckler]

@newpi - You cannot use a measure in a Page level filter.

[newpi]

edited my title. Can you review the post again please