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 s...
  • 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]
        )
)
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.