AddColumns with Summarize

[gpillai99]

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

[DataInsights · Accepted solution]

@gpillai99,

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]:

[DataInsights · Accepted solution]

@gpillai99,

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]:

[gpillai99 · Accepted solution]

Yes, this is very clear and works perfectly. Thanks!