Divide single value according to Distinct count of employees and departments

[Anonymous]

Hi there,

I'm trying to split a fixed value ( cost) for the departments of the company based on the number of employees per department.

I have a table with the salaryies of all employees, 1 row per employee per month and also have the department  in same table

What i'm trying to do is to split a certain cost (training cost for example) for all the departments, in %, based in how many employees per department.

Example: 

In this example, 9 employees, in 4 different Departments. I want to split the 5000,00 for the departments based in how many employees they have. 

is it possible ?

Thank you

[jgeddes · Accepted solution]

Using your sample data you can create a couple of measures.

One to calculate the Department Percentage

Department Percentage = 
//count the number of rows in the employeeTable based on the context provided i.e. Department Name rows.
var _departmentCount =
COUNTROWS(employeeTable)
//count all of the rows in the employeeTable
var _totalCount =
CALCULATE(
    COUNTROWS(employeeTable),
    ALL(employeeTable)
)
//divide the department count by the total count, return 0 if there is an error
var _percentage =
DIVIDE(
    _departmentCount,
    _totalCount,
    0
)
Return
//return the percentage calculation (remember to format as a percentage)
_percentage

and one to calculate the cost split

Cost Portion = 
//the cost amount to be split
var _totalCost =
5000
//split the cost based on the Department Percentage measure
var _portion =
_totalCost * [Department Percentage]
Return
//return the calculated portion, remember to format as currency
_portion

You would then end up with

[Anonymous]

Hi ,

Thank you for your reply. It works almost perfectly but something is missing (i forgot to mention one thing):

This happen because i have several months and years in the table, so wen i use the slicer to filter the  date, the mesasue Department Percentage does not calculate only for the selected month. In this print, the total% of October is 2.22% ... of the total of the year. I need it to be always 100% .

Thank you

[Anonymous]

I managed it! Just modified the Department Percentage measure to:

Department Percentage = 
//count the number of rows in the employeeTable based on the context provided i.e. Department Name rows.
var _departmentCount =
CALCULATE(COUNTROWS(employeeTable),calendar[Date] = MAX(calendar[Date]))
//count all of the rows in the employeeTable
var _totalCount =
CALCULATE(
    COUNTROWS(employeeTable),
    ALL(employeeTable), calendar[Date] = MAX(calendar[Date]))
)
//divide the department count by the total count, return 0 if there is an error
var _percentage =
DIVIDE(
    _departmentCount,
    _totalCount,
    0
)
Return
//return the percentage calculation (remember to format as a percentage)
_percentage

Thank you so much @jgeddes !