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 ,

Does the solution provided address your issue, or are you still experiencing the problem? Your feedback is important to the community. If your issue has been resolved, please mark the response as Accepted Solution to help others find it more easily.

Thank you.

[RenateBK]

Hello, I apologize for the delay, I have accepted the solution now!

Kind regards, 

Renate

[divyed]

Hello @RenateBK ,

Kindly use below dax to get desired result.

Average Sales per Department =

CALCULATE(

    DIVIDE(

        SUM(Test_Table[Sales]),

        DISTINCTCOUNT(Test_Table[Employee])

    ),

    REMOVEFILTERS(Test_Table[Date], Test_Table[Employee])

)

Here is the output : 

I hope this helps.

Kindly mark this as solution if this helps, kudos are appreciated.

Cheers.

Neeraj

[RenateBK]

Thank you for the help, this formula was definitely useful. I haven't learn much about removing filters so I will definitely read more up on this section.

FYI The formula you posted also included employees that had 0 sales registered, but had another column entry which was not supposed to be included in this calculation so I accepted the other solution for this problem: 

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

 However,  I think your formula will serve me very well at another point so again, thank you for your time 🙂 

Kind regards, Renate

[V-yubandi-msft]

Hi @RenateBK ,

Please review my response at your convenience to see if it meets your requirements. I have attached the PBIX file for better understanding. If it resolves your issue, please mark it as the accepted solution for the benefit of the community.

[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 · Accepted solution]

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!

[RenateBK]

Hello, thank you so much this was what I needed! I tried the same formula, but it failed as I had not included "VALUES(". 

If possible could you please explain why that was necessary to use here? I'm quite new with this and looking at the Power BI documentation I don't quite understand why using DISTINCTCOUNT could not solve the issue (without removing filters).

Kind regards, Renate

[AlexTheGreat]

Hi,

Nice to hear that it works!

DistinctCount() wouldn’t work here, because that just counts the number of unique employees, not their totals. If you try to get the average by doing Sum(sales)/ DistinctCount(Employee), you might get misleading results. For example: total sales = 600, 3 employees = average 200. But is that really the average per employee? Maybe Bob has 500, Stuart 100, and Kevin 0. so the real average of employee totals would be (500 + 100 + 0)/ 3 = 200, but in more complex scenarios (like with multiple layers: employee, team, floor, location) it often breaks and gives wrong insights.

That’s why values() is needed. Values(employee) creates a list of all unique employees in the current context. Then AverageX goes through each of them, calculates their individual total sales, and averages those totals.

It’s different from DistinctCount() that just counts unique values, it doesn’t give you a list to iterate over and sum up sales per employee.

Hope it helps! If you have any further questions please let me know. Happy to help!

[RenateBK]

Thank you for the explanation, that makes so much sense!

I appreciate the help a lot 🙂