How to determine how many customer are left standing per hour in a waiting room.

[jcmv007]

Hello every one need help with a Measure.

I need to calculate how many customers are left standing per hour in a waiting room. If there are more customers in the waiting room then there are available chairs, I would like to calculate how many are left standing.

Data is coming in from SQL Sever already filtered for the current day.

Each transaction has a creation hour calculated from the created date time field.

Not customer id is available.

Asume each transaction corresponds only to one customer

I have the following tables

Site Table

SiteID     Site Name          Total Qty of Chairs

L001      MAIN                   45

L002      SECONDARY      35

Transactions Table

TransactionID   Hour Created    SiteID   DateCreated

12345               9                        L001    02/12/2016

12456               8                        L001    02/12/2016

3456                 10                      L002    02/12/2016

James

[MattAllington]

Looks like a homework assignment. What approach are you thinking of?

[jcmv007]

Hi Matt,

What I tried so far is the following:

Max Available Seats = Calculate(Max(Total Qty of Chairs)

Customers per hour =Calculate(DistinctCount(TransactionID),HourCreated)

Standing Customers = IF([Customers per hour] >[Max Available Seats], [Customers per hour] - [Max Available Seats],0)

When placed on a Matrix Visual with the Hour Created as a row I get the correct quantity of Customers Standing but the total is not correct. It displays the total transaction quantity.

[MattAllington]

How is your data model set up?  ie how are your tables joined?

You should have a table that contains all the possible hours.  Do you have this?

Your Max Seats looks good.

You don't need distinctcount because there is nothing distinct to count.  All you need to count is how many rows are in the transactions table.  You don't need CALCULATE because the matrix provides the filtering by hour for you.  So just use a simple countrows

Total People = countrows(transactions)

So people standing = [Total People] - [Max Seats]

As long as you have location on columns and hours on rows, the matrix will do the filtering for you and it will work.