How to overcome ALL sensitivity to TOP N filtering on a visual?

[t_guet01]

Hi guys,

I am currently trying to display the average revenue over all product group per continent. Say, each continent has the same 50 product groups. I would like to display the average revenue over all product groups distinguished by continent. 

However, in my visual (matrix with product groups as rows and continents as columns) I use TOP N filtering in the filter bar to display only the top 10 product groups regarding revenue. I still want to display the overall average over all 50 product groups, not the average over only the top 10 externally filtered product groups. I tried to do this via ALL - doesn't work though. It shows the average just over the selected top 10 product groups, even though I declared "ALL" in my formula.

Curennt formula used:

Average revenue per continent =

CALCULATE (

AVERAGE ( 'Analysis'[revenue]),

Filter(ALL('Analysis'),'Analysis'[continent]=max('Analysis'[continent])))

Visual: 

Average revenue	Europa	Asia	...
Product Group 1	100	500	200
Product Group 2	100	500	200
...	100	500	200

Do you have any ideas?

Best

[V-lianl-msft · Accepted solution]

Hi @t_guet01 ,

Please try to ignore the crossfilter with other table.

If other table is used for filter,Please try to ignore the crossfilter with other table.

Try measure like below:

Measure = CALCULATE(AVERAGE(Analysis[revenue]),FILTER(ALL(Analysis),Analysis[continent]=MAX(Analysis[continent])),ALL(Table_GROUP[Group]))

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