2 Replies Latest reply on Oct 17, 2018 6:29 AM by Tom Hier

# How to Calculate Percentages Based on a Count of Like-Records in a Field

Hi.  Here is the situation.  I have a field called "04 Room Use Type Level 1" which describes various room types -- e.g., "Amphitheatre", "Lecture Hall", "Classroom", etc.  The database where this field resides is a database of individual course records (e.g., Nursing 101, etc.).  Each record (course) has meeting hours associated with it (e.g., Nursing 101 might meet for 4 hours per week).  I have a chart that shows for each room type the sum of total class meeting hours for that room type.  I'm now trying to figure out how to compute the percentage of hours used -- either by day or by week.  In algebraic terms, the calculation I need to create would be something like the following:

Sum(Total Class Hours per Week for a Particular Room Type) / [ Count(Rooms of That Type) * (Number of Available Hours Per Day (or per Week))]

To make this real, assume I have 5 Lecture Halls, and 7 Classrooms in the database.  I have 20 courses that meet in the 5 various Lecture Halls (with a total of 50 hours in those Lecture Halls), and I have 10 courses that meet in the 7 Classrooms (with a total of 40 hours in those Classrooms).

I'm trying to figure out what the calculated field would be that would give me results -- e.g.:

...add all course hours associated with Lecture Halls  (50 hours)

...add up the number of Lecture Halls in the "04  Room Use Type Level 1" field (5 halls)

...compute the total potential hours available to be scheduled (5 halls * 40 hours per hall per week)

...compute the room utilization of Lecture Halls:  50 hours used / (5 halls * 40 hours per hall per week) = 50 / 200 = 25% room utilization for Lecture Halls.

In the attached packaged workbook, the first sheet, labeled "START WITH THIS SHEET" shows the total hours scheduled in each room type.  Now I need to add up the number of rooms of that type in the "04 Room Use Type Level 1" field, multiply it by the total hours available per day or per week (which I already have as a parameter) and then compute the percentage.  I just don't know how to add the number of rooms by room type in a calculation.

Hope this makes sense.  Thanks.

(Looking forward to the conference next week!!!)

• ###### 1. Re: How to Calculate Percentages Based on a Count of Like-Records in a Field

Tom,

Your workbook is quite detailed, and so my apologies if I have missed the boat

or if am retreading ground you have already trod.

It would seem to me that your metrics can all be done with level of detail calculations

Overview: Level of Detail Expressions

which would enable you to directly specify how you want to aggregate your numbers.

As an example,

rooms of a particular Type1:

{ FIXED [04 Room Use Type Level 1] : COUNTD ( [04 Room No] ) }

total hours for a Type1:

[z Rooms per 04 type1] * [P.Total Hours Scheduled Per Day] * 5

total meeting hours:

{ FIXED [04 Room Use Type Level 1] : SUM ( [05 Meeting Hours Per Week ALL COURSES] ) }

Please see workbook v18.1 attached in the Forum Thread.

• ###### 2. Re: How to Calculate Percentages Based on a Count of Like-Records in a Field

Hi Swaroop,

Thanks so much for this solution.  I kind of figured LOD would be involved, but I couldn't quite figure out how to structure the calculations.  This is terrific!  Many thanks.

Best regards,

Tom Hier

1 of 1 people found this helpful