Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
Proud to be a Super User! | |
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
Proud to be a Super User! | |
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
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 !
User | Count |
---|---|
101 | |
90 | |
79 | |
70 | |
69 |
User | Count |
---|---|
112 | |
96 | |
96 | |
75 | |
72 |