How to calculate average sales based on employees within different departments

[RenateBK]

Hello, 

I'm currently working on a dashboard where I need to calculate the average sales within several different departments and I'm struggling with getting the average filtered based on departments.

It's all listed in one large dataset so there's several entries of the employee and department, and so far I keep going in circles where I'm getting wrong values. Currently I've managed to calculate the average sales per employee, but it cannot be filtered to department.

Example of how the dataset looks

Date	Employee	Sale	Department
01.01	A	100$	Team A
01.01	B	150$	Team B
02.01	A	90$	Team A
02.01	C	50$	Team A

I tried to set up by using the following

= DIVIDE(SUM(SalesTable[Sales), DISTINCTCOUNT(SalesTable[Department]))

What I want to see is the average sales based on department:

Team A = 120

Team B = 150

but what I get is

Team A = 240

Team B = 150

Total Average = 195

The issue I run into is that in the matrix when I set it up with department and the sales average, it doesn't filter the average based on department, it only gives me the total average at the bottom. 

Is there a way I can set up a filter in my equation so that it will filter correctly? Any help is appreciated.

[V-yubandi-msft]

Hi @RenateBK ,

Thank you for engaging with the Microsoft Fabric Community.  I tested your approach and successfully calculated the correct department-wise average sales. The crucial adjustment was ensuring the measure properly filtered by employee count using DISTINCTCOUNT(Employee), which resolved the previous aggregation issue.

Measure Used:

AverageSalesPerDept = 
DIVIDE(
    SUM(SalesTable[Sale]),
    DISTINCTCOUNT(SalesTable[Employee])
)

Output:

For more details, I’ve attached the .pbix  file feel free to check it out.

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

[AlexTheGreat]

Hi,

This will probably do the trick:

Average_Sales=
AVERAGEX(
VALUES(SalesTable[Employee]),
CALCULATE(SUM(SalesTable[Sale]))
)

 You can choose if you want the average for your employee or department (depending on your selected columns).

Hope it helps!