Hi, I have another issue with measures and averages that I'm trying to solve as follows:
Table 1 has the name of stores, departments and sales by department.
Table 2 has the name of stores, departments and how many people work in each department,
I'm trying to calculate the average of the sales by department.
I've got measures as follows:
Total £ = SUM(Table1[Sales])
Total staff = SUM (Table2[people])
£ per staff = DIVIDE([Total£],[Total staff])
I've also got two additional tables for the store and departments created using:
Departments = VALUES('Table 1'[Departments])
Stores= VALUES('Table 1'[Stores])
which are linked to both Table 1 and Table 2.
I've then got a matrix which shows
Store (from Stores Table) Total £ Total Staff Total £ per staff
A 547 243 2.25
B 65 30 2.17
I then want to apply a department via a filter (pulled from the Departments table) and calculate the average "total £ per staff" for all of the stores with that department. I've currently got:
Average £ per staff = AVERAGEX(Table 1,[£perstaff])
(which for the above example would be 2.21) but something is going wrong and the average is too low, it seems to be pulling in everything rather than just what has been filtered (note this is a simplified version and I have a couple of other filters to select the store in the first place - e.g. region/country which might be confusing it too?).
Does anyone have any suggestions?
