Forum Discussion
Bonus Calculation
You can use DAX to create calculated column and measures that count the occurrences of the bonus rates based on your specified conditions.
You need to create a calculated column that ranks the occurrences of jobs for each employee:
OccurrenceRank =
VAR Employee = [EmpID]
VAR Date = [Date]
RETURN
RANKX(
FILTER(
'Table',
'Table'[EmpID] = Employee
),
[Date],
,
ASC,
DENSE
)
This column will assign a rank to each job occurrence for every employee based on the date.
---
Next, create measures to count the number of 1 based on the specified occurrence ranges:
Count1s_First7 =
CALCULATE(
COUNTROWS('Table'),
'Table'[Bonus Rate] = 1,
'Table'[OccurrenceRank] <= 7
)
Count1s_8thTo12th =
CALCULATE(
COUNTROWS('Table'),
'Table'[Bonus Rate] = 1,
'Table'[OccurrenceRank] > 7,
'Table'[OccurrenceRank] <= 12
)
Count1s_13thAndAbove =
CALCULATE(
COUNTROWS('Table'),
'Table'[Bonus Rate] = 1,
'Table'[OccurrenceRank] > 12
)
These measures will count the number of 1s in the specified occurence ranges.
Thank you for the prompt response.
How will the Rank work with the slicer on date?