Forum Discussion
Power BI: Groupby using a Calculated Table
Hi community members, I have searched for two days but can't find anything... please help.
The data:
- Salary table: [EmployeeCode, MonthlyUSD, DateStart, DateEnd]
- A record applies to a_date, if a_date is between DateStart and DateEnd.
- Same EmployeeCode may have multiple record applies: ie.., given a date, one EmployeeCode could have multiple records with different MonthlyUSD.
My intention:
- Filter the table with a date (MaxContextDate): end of each month
- Group by EmployeeCode, and Sum MonthlyUSD: get the sum USD of each employee.
- Get the Median of [Sum Monthly USD] from the different employees
The measure:
MedianSalaryEmp =
VAR ContextDate = [MaxContextDate] //current context date
VAR TempTab = CALCULATETABLE(
Salary,
FILTER(ALL(Salary[DateStart]), Salary[DateStart] <= ContextDate ),
FILTER(ALL(Salary[DateEnd]), Salary[DateEnd] >= ContextDate || ISBLANK(Salary[DateEnd]) )
)
VAR GroupbyByTab = GROUPBY(
CALCULATETABLE(
Salary,
FILTER(ALL(Salary[DateStart]), Salary[DateStart] <= ContextDate ),
FILTER(ALL(Salary[DateEnd]), Salary[DateEnd] >= ContextDate || ISBLANK(Salary[DateEnd]) )
), [EmployeeCode], "SumUSD", SUMX(CURRENTGROUP(), [MonthlyUSD])
)
VAR Med = Median (GroupbyByTab[SumUSD])
Return Med
The error:
Table variable 'GroupbyByTab' cannot be used in current context because a base table is expected.
Can anyone guide me how to solve this problem?
Thanks!
2 Replies
- amitchandak
Super User
gavinma2022 , refer if these approaches can help
Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM
https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-or-end-date/ba-p/1503785Hi Amitchandak,
Thanks for replying.. But I can't understand the solution. My problem is that I don't know how to calculate the median of the column of a temp table....
