SUM of Distinct with date value

[VincentAK]

Hi all, I've read a couple of topics with similar questions, but didn't find the right solution for this problem yet. Hope you can help me out.

My goal is to calculate the occupancy rate of an exhibition. This is the sum of the visits per exhibition, per timeslot. The max visits per exhibition can varry per timeslot (and per exhibition). I have difficulties to calculate the max visits. I tried doing this with the DISTINCT function, but I got stuck.

Exhibition	Visits	Timeslot	MaxVisits
A	2	1-1-2018 08:00	15
A	1	1-1-2018 09:00	20
B	4	1-1-2018 10:00	20
A	2	2-1-2018 08:00	15
A	3	2-1-2018 08:00	15
A	5	2-1-2018 09:00	20
B	1	3-1-2018 08:00	30
B	1	3-1-2018 08:00	30

Help is very much appreciated! Thanks.

[v-chuncz-msft]

@VincentAK,

You may refer to the measure below.

Measure =
DIVIDE ( SUM ( Table1[Visits] ), MAX ( Table1[MaxVisits] ) )

[VincentAK]

Thanks for the reply! Unfortunatly this is not the solution I'm looking for. Sorry for not being clear in the description.

Every exhibition has a different max capacity. This also depends on the timeslot. I want to devide the total visitor by the total capacity. The thing is that some visitors book the same exhibition and the same timeslot, but are in seperate rows. 

For example, in the last two rows, there are 2 people going to the same exhibition, but are in different rows. The occupancy rate there would be 2/30 instead of 2/60.

This I want to do for every timeslot and every exhibition. In the end I want to calculate for every exhibition the average occupancy rate. 

Hope this clarifies.

[v-chuncz-msft]

@VincentAK,

Make sure it is a measure, not calculated column.

[VincentAK]

No, this isn't right. It takes the MAX, but instead it should take the sum of the distinct maxvisits.