cancel
Showing results 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

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.

Iris

1 ACCEPTED SOLUTION
Super User
4 REPLIES 4
Super User

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.

Super User

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

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" ?