Forum Discussion
snph1777
Helper V
Helper VDAX - Average (measure) based on most recent Date
My Power BI model has a single table (only). I need to develop 2 measures, one for Salary of an employee based on most recent effective date; the other for Average Salary of an employee, based on...
You can create a couple of measures like
Total Salary = VAR EmpWithAllSalaries = CALCULATETABLE( SUMMARIZE( Table1, Table1[EmployeeName], Table1[EffectiveDate], Table1[Salary] ), ALLEXCEPT( Table1, Table1[EmployeeName], Table1[EffectiveDate], Table1[Salary] ) ) VAR EmpWithLatestSalary = INDEX( 1, EmpWithAllSalaries, ORDERBY( Table1[EffectiveDate], DESC ), PARTITIONBY( Table1[EmployeeName] ) ) VAR Result = SUMX( EmpWithLatestSalary, Table1[Salary] ) RETURN Result Average Salary = VAR EmpWithAllSalaries = CALCULATETABLE( SUMMARIZE( Table1, Table1[EmployeeName], Table1[EffectiveDate], Table1[Salary] ), ALLEXCEPT( Table1, Table1[EmployeeName], Table1[EffectiveDate], Table1[Salary] ) ) VAR EmpWithLatestSalary = INDEX( 1, EmpWithAllSalaries, ORDERBY( Table1[EffectiveDate], DESC ), PARTITIONBY( Table1[EmployeeName] ) ) VAR Result = AVERAGEX( EmpWithLatestSalary, Table1[Salary] ) RETURN Result
johnt75
Super User
Super UserYou can create a couple of measures like
Total Salary =
VAR EmpWithAllSalaries = CALCULATETABLE(
SUMMARIZE(
Table1,
Table1[EmployeeName],
Table1[EffectiveDate],
Table1[Salary]
),
ALLEXCEPT( Table1,
Table1[EmployeeName],
Table1[EffectiveDate],
Table1[Salary]
)
)
VAR EmpWithLatestSalary = INDEX( 1, EmpWithAllSalaries, ORDERBY( Table1[EffectiveDate], DESC ), PARTITIONBY( Table1[EmployeeName] ) )
VAR Result = SUMX( EmpWithLatestSalary, Table1[Salary] )
RETURN Result
Average Salary =
VAR EmpWithAllSalaries = CALCULATETABLE(
SUMMARIZE(
Table1,
Table1[EmployeeName],
Table1[EffectiveDate],
Table1[Salary]
),
ALLEXCEPT( Table1,
Table1[EmployeeName],
Table1[EffectiveDate],
Table1[Salary]
)
)
VAR EmpWithLatestSalary = INDEX( 1, EmpWithAllSalaries, ORDERBY( Table1[EffectiveDate], DESC ), PARTITIONBY( Table1[EmployeeName] ) )
VAR Result = AVERAGEX( EmpWithLatestSalary, Table1[Salary] )
RETURN Result
