Forum Discussion
Bonus Calculation
Hello!
I have achieved below result using a matrix.
Above table, where bonus rate is 1.0, can be 1.5 or 2 also depending on some conditions.
Now, what I would like to achieve is,
1. Number of 1's from the first 7 occurences of jobs.
2. Number of 1's from the 8th to 12th occurences of jobs.
3. Number of 1's from the 13th and above occurences of jobs.
Please help me how to solve this.
Many thanks in advance.
2 Replies
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?
