Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Solved! Go to 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')
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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')
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Tom_G , Assume Number of Employees is a column
calculate(sum(Table[Number of Employees]) , filter(allselected(Table), Table[City] = max(Table[City]) ) )
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
91 | |
87 | |
84 | |
67 | |
49 |
User | Count |
---|---|
131 | |
110 | |
96 | |
70 | |
67 |