Hello everyone!
I'm a newbie discovering PowerBi and I'm currently struggling with creating a visual comparison of a unit vs peer group.
Let's say we are evaluating the status of late arrivals for a student. I have a table with a number of students and their arrival time each day. I have calculated columns with the number of minutes each student is late on each day, status "late"/"on time" and the delay arranged in delay ranges (1. on time, 2. <10min, 3. 10-20min, 4. >20min). to represent this graphically I created a system of traffic lights with 4 measures: green if the student is majorily on time, yellow if the delays a majorily in range 2, orange if the delays are majorily in range 3 and red if delays are majorily in range 4.
Example of the measure for orange traffic light:
Light_major_3 = if(or([Nbr_late]>0&&[Nbr_late_range_3]/[Nbr_late]>=0.5&&[Nbr_late_range_4]=0,and([Nbr_late_range_4]<>0,[Nbr_late_range_4]/[Nbr_late]<=0.1)),1,0) where nbr_late is count of records where the student is late and nbr_late_range_X is count of records in each delay range |
It works well for a given student. Now I want to show visual comparison, using trafic light system, of this student to its peer group which is dynamic and defined by slicers (all students, their promotion year, their class etc): for example selected student receives yellow light, but their peer group green.
If I use the same measure I already created, PowerBi considers the group of students as a whole, which is not specifically correct, while what I want is a kind of average of statuses for each student in this group. In Excel I would calculate each traffic light (1;0) for each student, then count (or simply sum up as possible values are 1 and 0) the number of each light and calculate weighted average (assigning 1 to green, 2 to yellow, 3 to orange and 4 to red).
Example for a group of 26 students based on 5382 observations :
| 4 green lights, 3 yellow, 10 orange and 9 red -> round((4*1+3*2+10*3+9*4)/26),0)=3-> would give orange light (while current measure as whole gives red light) |
I'm actually not even sure if it's possible to do the same in PowerBI, provided that the lights for each student are variable based on other measures and I don't know how to count them.
