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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
VincentAK
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.

 

ExhibitionVisitsTimeslotMaxVisits
A21-1-2018 08:0015
A11-1-2018 09:0020
B41-1-2018 10:0020
A22-1-2018 08:0015
A32-1-2018 08:0015
A52-1-2018 09:0020
B13-1-2018 08:0030
B13-1-2018 08:0030

 

Help is very much appreciated! Thanks.

4 REPLIES 4
v-chuncz-msft
Community Support
Community Support

@VincentAK,

 

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.

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.

@VincentAK,

 

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.

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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