Using a SUMMARIZE Table within a dax formula

[Anonymous]

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 · Accepted solution]

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 · Accepted solution]

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

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!

[johnt75 · Accepted solution]

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]

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