Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
IrisW
Regular Visitor

Average occupancy

Hi, I have a challenge in Power BI.

I have a table (name is "Export") containing the following information:
- date
- player
- start time (= [Reservering Tijd start])
- end time (= [Reservering Tijd t/m])
- reservation number
- tennis court (= [Baan])

 

For examble:

Date:             Player:   Start time:   End time:   Res.nr.   Court

16-5-2024     Mike      8 am            9 am          001       1

16-5-2024     Steve     8 am            9 am          001       1

16-5-2024     John      8 am            9 am          001       1

16-5-2024     Robin    8 am            8.30 am          002       2

16-5-2024     Rick       8 am            8.30 am          002       2

17-5-2024     Patrick   8 am            9 am          003       1

17-5-2024     Dennis   8 am            9 am          003       1

 

On May 16 at 8 a.m. the court occupancy is 2. On May 16 at 8.45 a.m. the court occupancy is 1 (just Mike, steve and John).
On May 17 at 8 a.m. the court occupancy will be 1.
On average I have an occupancy of 1.5 at 8 a.m (sometimes 1 court, other times 2 courts) and at 9.15am an average occupancy of 1 (at 9.15am a maximum of 1 court is occupied.)

 

I also have a time table (name is 'Timetable') so that I have all possible times between 8am and 11pm ready.

 

I want to make a graph showing the average tennis court occupancy at each time. So on the x-axis all times from 8:00 am to 11:00 pm and on the y-axis the average tennis court occupancy.

 

I managed to do this by adding the following measurement to muy Export-table:

Baanbezetting =
CALCULATE(DISTINCTCOUNT(Export[Baan]),
   FILTER(values(Export[Reservering Tijd start]),Export[Reservering Tijd start]<=max(TimeTable[Tijd])),
  FILTER(values(Export[Reservering Tijd t/m]),(Export[Reservering Tijd t/m]>=min(TimeTable[Tijd]))))

 

If I select a number of dates (for example, all dates in April), it will display a full tennis court occupancy (of 100%) at 8 p.m. (busiest time) because in that date range all runways were occupied somewhere at 8 p.m. This should be about 95%.

 

I just can't manage to show the average per time in the graph.

 

Thanks for your thoughts.

 

Iris

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

4 REPLIES 4
lbendlin
Super User
Super User

lbendlin_0-1715963229471.png

 

IrisW
Regular Visitor

Yes, I'm sorry. I meant "for each Start time". One court is reserved per 15 minutes. So the starting times could be every 15 minutes. 

Which locale is this?  I can't find any locale that uses 8.30 am notation.

lbendlin
Super User
Super User

 

 

I just can't manage to show the average per time in the graph.

 

 

You need to quantify "time".  Per what? Per hour, per 30 or 15 minute slot? Or maybe you didn't mean to say "average", rather "for each point in time" ?

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.