Forum Discussion

gpillai99's avatar
gpillai99
Frequent Visitor
5 years ago
Solved

AddColumns with Summarize

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

 

 

 

Need Help
  • DataInsights's avatar
    DataInsights
    5 years ago

    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's avatar
    gpillai99
    5 years ago

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

2 Replies

  • DataInsights's avatar
    DataInsights
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      gpillai99
      Frequent Visitor
      5 years ago

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