Create a time sensitive matrix using measure

[Nabil_DS]

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 (could be one or multiple).

• Session Name: The name of the session.

• Session Status: Indicates whether the session was attended or not (Attended or Not Attended).

I created the following measures:

1. Attendance Rate: This measure calculates the attendance rate per company. It is computed by dividing the total number of company representatives who attended sessions by the total number of representatives invited to sessions.

2. Unique Attendees Per Company: This measure counts the unique representatives who attended at least one session for each company.

Problem and Solution

The challenge is that small companies are invited to fewer sessions and can easily achieve a 100% attendance rate if they attend all their sessions. In contrast, large companies, invited to many sessions, may attend a large number of them but appear to have a lower attendance rate due to the sheer volume of sessions they are invited to. This could misrepresent the data, as small companies would seem to perform better purely because of their smaller scale.

Your explanation is mostly clear, but there are a few places where it can be clarified further to make your intent and approach more precise. Here’s a revised version of what you’re trying to say:

Now, I want to use the Weighted Attendance Rate measure to segment companies into five buckets (segments). To do this:

1. I calculate the range of weighted attendance rates (i.e., Max - Min) and divide it into five equal intervals.

2. Based on these intervals, companies will be assigned to one of five segments, with Segment 1 having the lowest rates and Segment 5 having the highest.

Additionally, I want to create five buckets (segments) for the Number of Unique Attendees per Company using a similar approach:

1. I calculate the range of unique attendee counts (i.e., Max - Min) and divide it into five equal intervals.

2. Based on these intervals, companies will be assigned to one of five segments, with Segment 1 having the smallest number of attendees and Segment 5 having the largest.

• Circular Dependency Error: When I tried to crens for the segments, I encountered a circular dependency error.

• Using Measures: I then tried to create measures and an isolated table for the segmentation logic, but I was still unable to achieve the desired output.

I need help understanding how to correctly implement this segmentation logic without running into circular dependency issues, and how to structure my data/model effectively to achieve the desired segmentation.

the output results should look like this 

Weighted attendance segments	0-100 attendees	100-200 attendees	200-300 attendees	300-400 attendees
0-25	5	8	33	77
25-50	2	5	12	18
50-75	3	3	6	32
75-100	7	9	2	54

[Kedar_Pande]

@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

[DataNinja777]

@Nabil_DS ,

First, calculate the attendance rate by dividing the number of attended sessions by the total sessions. Use this DAX formula:

Attendance Rate = 
DIVIDE(
    CALCULATE(COUNTROWS('Session'), 'Session'[Session Status] = "Attended"),
    COUNTROWS('Session'),
    0
)

Next, calculate the unique attendees per company by counting the distinct representatives who attended at least one session. Use this DAX formula:

Unique Attendees Per Company = 
CALCULATE(
    DISTINCTCOUNT('Session'[Client Representative]),
    'Session'[Session Status] = "Attended"
)

Then, calculate the minimum and maximum values for attendance rates and unique attendees across all companies to determine their ranges. For the attendance rate:

Min Attendance Rate = 
MINX(ALL('Session'[Client Name]), [Attendance Rate])

Max Attendance Rate = 
MAXX(ALL('Session'[Client Name]), [Attendance Rate])

For unique attendees:

Min Unique Attendees = 
MINX(
    VALUES('Session'[Client Name]),
    CALCULATE(
        DISTINCTCOUNT('Session'[Client Representative]),
        'Session'[Session Status] = "Attended"
    )
)

Max Unique Attendees = 
MAXX(
    VALUES('Session'[Client Name]),
    CALCULATE(
        DISTINCTCOUNT('Session'[Client Representative]),
        'Session'[Session Status] = "Attended"
    )
)

Next, calculate the range for each metric and divide it by five to determine the bucket intervals. For attendance rates:

Attendance Rate Range = 
[Max Attendance Rate] - [Min Attendance Rate]

Bucket Interval = 
DIVIDE([Attendance Rate Range], 5)

Next, calculate the range for each metric and divide it by five to determine the bucket intervals. For attendance rates:

Attendance Rate Range = 
[Max Attendance Rate] - [Min Attendance Rate]

Bucket Interval = 
DIVIDE([Attendance Rate Range], 5)

For unique attendees:

Unique Attendees Range = 
[Max Unique Attendees] - [Min Unique Attendees]

Unique Attendees Bucket Interval = 
DIVIDE([Unique Attendees Range], 5)

Finally, assign each company to a bucket based on their attendance rate or unique attendee count. For attendance rate:

Attendance Rate Bucket = 
VAR CurrentRate = [Attendance Rate]
VAR MinRate = [Min Attendance Rate]
VAR Interval = IF([Bucket Interval] > 0, [Bucket Interval], 1)
RETURN
    IF(
        CurrentRate < MinRate,
        1,
        CEILING(DIVIDE(CurrentRate - MinRate, Interval), 1)
    )

For unique attendees:

Unique Attendees Bucket = 
VAR CurrentAttendees = 
    CALCULATE(
        DISTINCTCOUNT('Session'[Client Representative]),
        'Session'[Session Status] = "Attended"
    )
VAR MinAttendees = [Min Unique Attendees]
VAR Interval = IF([Unique Attendees Bucket Interval] > 0, [Unique Attendees Bucket Interval], 1)
RETURN
    IF(
        CurrentAttendees < MinAttendees,
        1,
        CEILING(DIVIDE(CurrentAttendees - MinAttendees, Interval), 1)
    )

These steps and formulas ensure dynamic bucketing, handling edge cases like zero intervals or values below the minimum boundary.

The resulting output is as shown below:

To address your requirement for a time-sensitive measure, you can include a date field in your analysis and establish a relationship with the calendar table. This will ensure that the calculation aligns with the time dimension filter.

I have attached an example pbix file for your reference.

Best regards,

[Nabil_DS]

Thanks a lot @DataNinja777 

I have already created the measures but the output I want is something else; I want a matrix where rows represent weighted attendance rate segments and columns represent the unique attendee segments Weighted Attendance Rate Segments

Weighted attendance segments	0-100 attendees	100-200 attendees	200-300 attendees	300-400 attendees
0-25	5	8	33	77
25-50	2	5	12	18
50-75	3	3	6	32
75-100	7	9	2	54