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]
        )
)
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!