March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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.
You may refer to the measure below.
Measure = DIVIDE ( SUM ( Table1[Visits] ), MAX ( Table1[MaxVisits] ) )
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.
Make sure it is a measure, not calculated column.
No, this isn't right. It takes the MAX, but instead it should take the sum of the distinct maxvisits.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
134 | |
90 | |
90 | |
66 | |
58 |
User | Count |
---|---|
201 | |
137 | |
107 | |
70 | |
68 |