Forum Discussion

Nabil_DS's avatar
Nabil_DS
New Member
1 year ago

Create a time sensitive matrix using measure

Hi All,   I have a dataset that includes the following columns:   • Client Name: The name of the client company. • Client Representatives: The names of representatives associated with the client...
Data model
Kedar_Pande's avatar
Kedar_Pande
Icon for Super User rankSuper User
1 year ago

Nabil_DS 

Create Measures:

Weighted Attendance Rate = 
DIVIDE(
SUMX(
FILTER('Session Data', 'Session Data'[Session Status] = "Attended"),
1
),
SUMX(
'Session Data',
1
)
)
Unique Attendees = 
DISTINCTCOUNT('Session Data'[Client Representatives])

 

Weighted Attendance Segment = 
VAR MinRate = MINX(ALL('Session Data'), [Weighted Attendance Rate])
VAR MaxRate = MAXX(ALL('Session Data'), [Weighted Attendance Rate])
VAR Range = MaxRate - MinRate
VAR Step = Range / 5

VAR Bucket = 
SWITCH(
TRUE(),
[Weighted Attendance Rate] <= MinRate + Step, 1,
[Weighted Attendance Rate] <= MinRate + 2 * Step, 2,
[Weighted Attendance Rate] <= MinRate + 3 * Step, 3,
[Weighted Attendance Rate] <= MinRate + 4 * Step, 4,
5
)

RETURN Bucket
Unique Attendees Segment = 
VAR MinAttendees = MINX(ALL('Session Data'), [Unique Attendees])
VAR MaxAttendees = MAXX(ALL('Session Data'), [Unique Attendees])
VAR Range = MaxAttendees - MinAttendees
VAR Step = Range / 5

VAR Bucket = 
SWITCH(
TRUE(),
[Unique Attendees] <= MinAttendees + Step, 1,
[Unique Attendees] <= MinAttendees + 2 * Step, 2,
[Unique Attendees] <= MinAttendees + 3 * Step, 3,
[Unique Attendees] <= MinAttendees + 4 * Step, 4,
5
)

RETURN Bucket

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn