Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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:
Solved! Go to 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
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
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]
)
)
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!
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
)
Hey john, thank you very much. The solution works perfectly fine, will look into the TREATAS Function for these kinds of problems more.
User | Count |
---|---|
18 | |
18 | |
14 | |
14 | |
13 |
User | Count |
---|---|
17 | |
14 | |
11 | |
10 | |
8 |