Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all
Newbie here looking for some guidance. I'm trying to figure out what is the best DAX approach in solving the following problem. Any help from experts in this community is much appreciated.
I have a table with that has timesheet data submitted by employees to record their time spent on customers. My goal is to proportionally allocate each employees cost to the respective customer accounts they worked on. The output will simply show two columns Customers and Allocated labour cost. Here is an snapshot of how I'm doing it in excel now. I tried using SummarizeColumns with AddColumns but could not figure out the DAX code. Thanks
Solved! Go to Solution.
Try this solution.
1. Data model:
2. Measures:
Billable Hours = SUM ( LaborHours[Billable Hours] )
Non-Billable Hours = SUM ( LaborHours[Non-Billable Hours] )
Allocated Labor Cost =
VAR vBaseTable =
ADDCOLUMNS (
SUMMARIZE (
LaborHours,
Customer[Customer ID],
Employee[Employee ID],
Employee[Labor Cost]
),
"@CustomerHours", [Billable Hours] + [Non-Billable Hours]
)
VAR vEmpHoursTable =
ADDCOLUMNS (
vBaseTable,
"@EmployeeHoursTotal", CALCULATE ( [Billable Hours] + [Non-Billable Hours], ALL ( Customer ) )
)
VAR vPercentTable =
ADDCOLUMNS (
vEmpHoursTable,
"@Percent", DIVIDE ( [@CustomerHours], [@EmployeeHoursTotal] )
)
VAR vAllocationTable =
ADDCOLUMNS ( vPercentTable, "@Allocation", [@Percent] * Employee[Labor Cost] )
VAR vResult =
SUMX ( vAllocationTable, [@Allocation] )
RETURN
vResult
3. Create visual using Customer[Customer ID]:
Proud to be a Super User!
Yes, this is very clear and works perfectly. Thanks!
Try this solution.
1. Data model:
2. Measures:
Billable Hours = SUM ( LaborHours[Billable Hours] )
Non-Billable Hours = SUM ( LaborHours[Non-Billable Hours] )
Allocated Labor Cost =
VAR vBaseTable =
ADDCOLUMNS (
SUMMARIZE (
LaborHours,
Customer[Customer ID],
Employee[Employee ID],
Employee[Labor Cost]
),
"@CustomerHours", [Billable Hours] + [Non-Billable Hours]
)
VAR vEmpHoursTable =
ADDCOLUMNS (
vBaseTable,
"@EmployeeHoursTotal", CALCULATE ( [Billable Hours] + [Non-Billable Hours], ALL ( Customer ) )
)
VAR vPercentTable =
ADDCOLUMNS (
vEmpHoursTable,
"@Percent", DIVIDE ( [@CustomerHours], [@EmployeeHoursTotal] )
)
VAR vAllocationTable =
ADDCOLUMNS ( vPercentTable, "@Allocation", [@Percent] * Employee[Labor Cost] )
VAR vResult =
SUMX ( vAllocationTable, [@Allocation] )
RETURN
vResult
3. Create visual using Customer[Customer ID]:
Proud to be a Super User!
Yes, this is very clear and works perfectly. Thanks!
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |