Power BI: Groupby using a Calculated Table

gavinma2022 — Fri, 25 Nov 2022 07:38:54 GMT

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!

Re: Power BI: Groupby using a Calculated Table

gavinma2022 — Fri, 25 Nov 2022 09:44:56 GMT

Hi 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....

topic Power BI: Groupby using a Calculated Table in DAX Commands and Tips

Power BI: Groupby using a Calculated Table

Re: Power BI: Groupby using a Calculated Table

Re: Power BI: Groupby using a Calculated Table