Average by category measure

[Tom_G]

I have a table like below.

Shop    Cities                Number of Employees

A             New York                         5

B             Paris                                 6

C             New York                         7

D            London                             5

E             Paris                                 6

F             New York                          8

I want to create a measure to calculate the Average Number of Employees per City and put it in a table visual like below.

Shop    Number of Employees     Avg Number of Employees per City

A                         5                                                      6.6

B                         6                                                      6

C                         7                                                      6.6

D                         5                                                      5

E                         6                                                       6

F                          8                                                     6.6

I don’t want to create a column for the average number of employees per city and then a measure referencing it cause I need a dynamic measure that can be sliced by other categories not shown above, like shop type for example.

What i'm struggling with is how to get the avg per city on each line of the table visual, when the city is not in the visual or in the tab slicers?

Thanks for any help

[Fowmy · Accepted solution]

@Tom_G 

Try this measure:

Avg Number of Employees per City = 
VAR _CITY = MAX('Table'[City] ) RETURN
CALCULATE(
    AVERAGE('Table'[NumberofEmployees]),
    'Table'[City] = _CITY,
    REMOVEFILTERS('Table') 

)

[Tom_G]

Hi @Fowmy thanks for the measure it works well! 

[amitchandak]

@Tom_G , Assume Number of Employees is a column

calculate(sum(Table[Number of Employees]) , filter(allselected(Table), Table[City] = max(Table[City]) ) )