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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## SUM of Distinct with date value

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.

4 REPLIES 4
Community Support

You may refer to the measure below.

```Measure =
DIVIDE ( SUM ( Table1[Visits] ), MAX ( Table1[MaxVisits] ) )
```
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

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.

Community Support

Make sure it is a measure, not calculated column.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

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

## Helpful resources

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors