Distributing Attendance across Time & Sum

[ExcellentTalkie]

Hello all, 

I have a list of movie start times and attendance values. There's also a table called "Clock" with a row for every minute of the day. Clock is connected to the movies table by the Time/Start Time. I'd like to determine, using a normal distribution, how many people are arriving at a given time for any movie. 

For instance, if I have a table of showtimes like below: 

Start Time	Film	Attendance
5:50	Dune 2	250
6:00	Madame Web	50
6:20	Dune 2	300
6:30	One Love	100

I want to be able to figure out how many people will be arriving for their movie at 6:10, which would potentially include people from all of these showings. 

I'd like to create a measure in the Clock table that tells me how many people are arriving at a particular time using a normal distribution function. The mean should be the mean time attendees arrive relative to the start of the movie and the standard deviation being minutes plus or minus that mean. So the mean is "most people show up <x> minutes early/late" and the standard deviation is "most people show up within <x> minutes of the actual start time."

	Minutes	Norm.Dist
	-30	10.93%
	-20	21.30%
Mean	-10	26.60%
Start Time	0	21.30%
	10	10.93%
	20	3.60%
	30	0.76%
	Total	95.42%
	Mean	St.Dev
	-10	15

So I'd like 6:10 to be as follows: 

Start Time	Film	Attendance	Mins +/-	Percent	Attendance
5:50	Dune 2	250	20	3.60%	9
6:00	Madame Web	50	10	10.93%	5
6:20	Dune 2	300	-10	26.60%	80
6:30	One Love	100	-20	21.30%	21
Time	Mean	St.Dev			Total
6:10	-10	15			115

I rounded the final attendance numbers for simplicity. The result of the measure should be 115. 

Is there a way to do this as a measure? 

Thanks!

[123abc]

To accomplish this task in Power BI, you can create a measure in the Clock table that calculates the number of attendees arriving at a particular time using a normal distribution function. Here's how you can do it step by step:

1. Calculate Mean and Standard Deviation: You have already calculated the mean and standard deviation for the distribution. You can define these values in separate measures in your model.

2. Create a Measure for Normal Distribution: Now, you need to create a measure that calculates the attendance based on the normal distribution. You can use the NORM.DIST function in DAX to calculate the probability density function at a given point.

3. Sum Attendance for Each Movie: After calculating the distribution for each movie, you need to sum up the attendance for all the movies that have showtimes around the given time.

Here's how you can implement these steps:

Mean = -10
Standard_Deviation = 15

Attendance_Normalized =
VAR ZScore = (Clock[Minutes] - [Mean]) / [Standard_Deviation]
RETURN
NORM.DIST(ZScore, 0, 1, FALSE)

Total_Attendance =
CALCULATE(
SUM('Movies'[Attendance]),
FILTER(
'Movies',
Clock[Start Time] = 'Movies'[Start Time] &&
Clock[Minutes] = 6*HOUR('Movies'[Start Time]) + MINUTE('Movies'[Start Time])
)
)

Total_Attendance_By_Time =
SUMX(
VALUES(Clock),
[Total_Attendance] * [Attendance_Normalized]
)

Make sure to replace 'Movies' with the name of your movie table. This measure Total_Attendance_By_Time should give you the total attendance at a specific time based on the normal distribution. You can then use this measure in your visualizations or calculations.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.