Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Measure for Group by matrix value

Hi all,

 

I hit the wall while creating a matrix using measure value. This is the senario. I have a staff schedule data table. From there, I created  4 measures - active scheudle, attended schedule, pecentage, percentage group as shown below. 

 

1. Active Shifts = CALCULATE(COUNTROWS(Staff_Schedule), Staff_Schedule[ActiveSchedule] = "Yes")

2. Attended Shifts = CALCULATE(COUNTROWS(Staff_Schedule), (Staff_Schedule[ArrivalStatus] = "On Time" || Staff_Schedule[ArrivalStatus] = "Late Arrival"), Staff_Schedule[ActiveSchedule] = "Yes")

3. Percentage = ROUND([Attended Shifts]/[Active Shifts]*100,0)

4. Percentage Group =

    SWITCH(TRUE,

    [Percentage] <=20, "0-20",

    [Percentage] >=21 && [Percentage] <= 40, "21-40",

    [Percentage] >=41 && [Percentage] <= 60, "41-60",

    [Percentage] >=61 && [Percentage] <= 80, "61-80",

    [Percentage] >=81 && [Percentage] <= 100, "81-100"

    )

 

This is the matrix created with the measures.

This is the visual setting. * EmplolyeeID is a column from the staff schedule data table.

 

I need to count the rows of Percentage Group by each pecentage group (e.g. 0-20, 21-40....), and make a matrix like this with the counts. 

 

Anyone has an idea?

 

Thanks!

Eliot

 

 

Need Help

9 Replies

  • gmsamborn's avatar
    gmsamborn
    Super User
    2 years ago

    Hi Anonymous 

     

    Would it be possible to show some sample data for 'Staff_Schedule' as well as the expected results for that sample data?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi gmsamborn 

     

    The "Staff_Schedule" has columns such as Employee ID, Schedule Date, Shift, First Name, Last Name, Check-in time, Check-out time, Arrival Status, Departure Status, and so on. Each row stores one distinct scheudle but each employee can have mutiple schedules (rows). What I am trying to achieve is 1) pulling the attendance percentage of each employee ([Attended Shifts]/[Active Shifts]*100), 2) categorize the percentage into 5 categories  0-20, 21-40, 41-60, 61-80, 81-100, and 3) count how many numbers are within each category. As shown above, I was able to achieve 1) & 2) but not 3). 

     

    3) should look like this

    Thank you so much!

    Eliot

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous 

     

    Here is my testing.

     

    My sample:

     

     

    Create a measure as follows

    SUM = 
VAR _sum = SUMMARIZE(ALL(Staff_Schedule), Staff_Schedule[employeeID], "per", [Percentage Group])
RETURN
COUNTX(FILTER(_sum, [Percentage Group] = MAX('Table'[Percentage Group])), [employeeID])

     

    Is this the result you expect?

     

    If I've misunderstood you, please provide detailed sample data: How to provide sample data in the Power BI Forum - Microsoft Fabric Community .Please remove any sensitive data in advance.

     

    Best Regards,
    Community Support Team _Yuliax

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Thank you so much Anonymous !! It works perfect! You're my life saver 🙂

       

      Just one thing you might be able to help! This is the clustered column chart I created. Do you know how to make the x-axis order "0-20", "21-40", "41-60", "61-80", "81-100"?

       

      Thanks!

      Eliot