How do I count the number of occurrences that a value in a Power BI matrix > X

user_14587475 0
Viewed 2117

I am trying to create a measure that counts the number of occurrences where any employee worked in excess of 45 hours in any week. I've looked at other posted questions and can't seem to connect the dots to my specific question. The following matrix shows the total hours worked by employee by week:

  • Employees and Hours (the rows and values in the matrix, respectively) reside in the same table called "Power BI Upload"
  • Week Number (Columns in the matrix) reside in a separate table called "Date Table"

Weekly Hours by Employee

My desired total row would show:

  • Week 30 has 2 total occurrences (50 and 48)
  • Week 31 has 3 total occurrences (60, 54, 47)
  • Week 32 has 3 total occurrences (48, 47, 47)
  • Week 33 has 5 total occurrences (46, 47, 72, 64, 68)
  • Week 34 has 5 total occurrences (48, 55, 56, 67, 62)

I hope I am being clear. Thank you very much for your help.

dax powerbi matrix
1 Answers

Add a DAX measure to Power BI Upload to count those employees with greater than 45 hours in a week as follows;

Over45 = CALCULATE(COUNT('Power BI Upload'[Employee Name]),FILTER('Power BI Upload',[Hours]>45))

That'll allow you to take your matrix data of

Hours Worked Matrix

and turn it into

Measure result over 45

user_3565060
Related