Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Using a SUMMARIZE Table within a dax formula

Dear Power BI Experts, 

i have problems writing this dax formula. I want to group one table (FCT_CONTRACT_EMPLOYEE, first picture) by employee_id to get the average daily rate of an employee. This should be multiplied with planned_working_days in another table (FCT_TIME_PLANNING_EMPLOYEE_LEGACY, second picture) based on the lookup column employee_id.

 

 

Right now it looks like this. The error says i cant use "EmployeeAvgDailyRate" because a base table is expected instead, but i thought that SUMMARIZE function works like that:

 

Hochrechnung Plan-Werte =
VAR EmployeeAvgDailyRate =
    SUMMARIZE(
        FCT_CONTRACT_EMPLOYEE,
        FCT_CONTRACT_EMPLOYEE[employee_id],
        "average_daily_rate", AVERAGE(FCT_CONTRACT_EMPLOYEE[daily_rate])
    )
RETURN
    SUMX(
        FCT_TIME_PLANNING_EMPLOYEE_LEGACY,
        FCT_TIME_PLANNING_EMPLOYEE_LEGACY[PLANNED_WORKING_DAYS] *
        LOOKUPVALUE(
            EmployeeAvgDailyRate[average_daily_rate],
            EmployeeAvgDailyRate[employee_id],
            FCT_CONTRACT_EMPLOYEE[EMPLOYEE_ID]
        )
    )
  • johnt75's avatar
    johnt75
    3 years ago

    You can't use LOOKUPVALUE with a variable, it has to be a base table. Try

    Hochrechnung Plan-Werte =
SUMX (
    FCT_TIME_PLANNING_EMPLOYEE_LEGACY,
    VAR CurrentEmployee = FCT_TIME_PLANNING_EMPLOYEE_LEGACY[Employee ID]
    VAR AvgDailyRate =
        CALCULATE (
            AVERAGE ( FCT_CONTRACT_EMPLOYEE[daily_rate] ),
            TREATAS ( { CurrentEmployee }, FCT_CONTRACT_EMPLOYEE[employee_id] )
        )
    RETURN
        FCT_TIME_PLANNING_EMPLOYEE_LEGACY[PLANNED_WORKING_DAYS] * AvgDailyRate
)
  • tamerj1's avatar
    tamerj1
    3 years ago

    Anonymous 
    Please try

    Hochrechnung Plan-Werte =
SUMX (
    VALUES ( FCT_CONTRACT_EMPLOYEE[employee_id] ),
    CALCULATE ( AVERAGE ( FCT_CONTRACT_EMPLOYEE[daily_rate] ) )
        * SUMX (
            FILTER (
                FCT_TIME_PLANNING_EMPLOYEE_LEGACY,
                FCT_TIME_PLANNING_EMPLOYEE_LEGACY[EMPLOYEE_ID] = FCT_CONTRACT_EMPLOYEE[employee_id]
            ),
            FCT_TIME_PLANNING_EMPLOYEE_LEGACY[PLANNED_WORKING_DAYS]
        )
)

4 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago

    You can't use LOOKUPVALUE with a variable, it has to be a base table. Try

    Hochrechnung Plan-Werte =
SUMX (
    FCT_TIME_PLANNING_EMPLOYEE_LEGACY,
    VAR CurrentEmployee = FCT_TIME_PLANNING_EMPLOYEE_LEGACY[Employee ID]
    VAR AvgDailyRate =
        CALCULATE (
            AVERAGE ( FCT_CONTRACT_EMPLOYEE[daily_rate] ),
            TREATAS ( { CurrentEmployee }, FCT_CONTRACT_EMPLOYEE[employee_id] )
        )
    RETURN
        FCT_TIME_PLANNING_EMPLOYEE_LEGACY[PLANNED_WORKING_DAYS] * AvgDailyRate
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hey john, thank you very much. The solution works perfectly fine, will look into the TREATAS Function for these kinds of problems more.

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Anonymous 
    Please try

    Hochrechnung Plan-Werte =
SUMX (
    VALUES ( FCT_CONTRACT_EMPLOYEE[employee_id] ),
    CALCULATE ( AVERAGE ( FCT_CONTRACT_EMPLOYEE[daily_rate] ) )
        * SUMX (
            FILTER (
                FCT_TIME_PLANNING_EMPLOYEE_LEGACY,
                FCT_TIME_PLANNING_EMPLOYEE_LEGACY[EMPLOYEE_ID] = FCT_CONTRACT_EMPLOYEE[employee_id]
            ),
            FCT_TIME_PLANNING_EMPLOYEE_LEGACY[PLANNED_WORKING_DAYS]
        )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hey, thanks a lot, it works fine! Using the FILTER function for the lookup and the VALUES function to calculate the average daily rate is a nice way to solve this problem!