Max of Sum of an Expression

[jojo54]

Hi there,

A bit of background - I'm trying to work out how many people are in a cafe at any one time based on their entry and exit times (see sample below).  I have managed to do this by creating a new table with crossjoin (Client,Time) 

Client

Name	Company	Entry Time	Exit Time
John	A	8:00	9:30
James	A	9:00	10:30
Vincent	B	10:00	11:30
Tom	C	10:30	12:00
Sarach	B	10:30	12:00
Jane	A	9:00	10:30
Catherine	C	9:30	11:00
Lana	A	8:30	11:30

Time

Time

8:00

8:30

9:00

9:30

10:00

Then using IF statements to create an indicator column, to create a table visual:

Name	Company	Entry Time	Exit Time	8:00	8:30	9:00	9:30	10:00
John	A	8:00	9:30	1	1	1	1
James	A	9:00	10:30			1	1	1
Vincent	B	10:00	11:30					1
Tom	C	10:30	12:00
Sarach	B	10:30	12:00
Jane	A	9:00	10:30			1	1	1
Catherine	C	9:30	11:00				1	1
Lana	A	8:30	11:30		1	1	1	1

What i want to do now is group by Company (which I can) and work out what is the maximum client in the cafe during the day. So I want to work out the "Max" column below.  I'm complete stumped as to how I do this!  Appreciate any insight / help this forum can provide!

Company	8:00	8:30	9:00	9:30	10:00	Max
A	1	2	4	4	3	4
B	0	0	0	0	1	2
C	0	0	0	1	1	2

[AnthonyTilley]

do you have a picture of your data model 

this shold be easy to accomplish but it depends how your data is formated 

for example if your table is as you have shown above and you have seperate colunms for each time stamp and each of these colunms is added to a matrixs as for example a eash colunm = sum('table'[8:00]) in this instance you can nest a lot of max formulas or if formulas. 

for example

Measure = max(max(sum('Table'[8:00]),sum('Table'[8:30])),MAX(sum('Table'[9:00]),sum('Table'[9:30])))

as max only allows 2 values you need to nest many to got to a full days worth 

or use an if statment but same problem 

If ([ColumnA] > [ColumnB], if ([ColumnA] > [ColumnC], [ColumnA], [ColumnC]), if ([ColumnB] > [ColumnC], [ColumnB], [ColumnC]))

the alternative is to unpiviot your data

so you have 

NameCompanyEntry TimeExit TimeIntervalValue

John	A	08:00:00	09:30:00	8:00	1
John	A	08:00:00	09:30:00	8:30	1
John	A	08:00:00	09:30:00	9:00	1
John	A	08:00:00	09:30:00	9:30	1
James	A	09:00:00	10:30:00	9:00	1
James	A	09:00:00	10:30:00	9:30	1
James	A	09:00:00	10:30:00	10:00	1
Vincent	B	10:00:00	11:30:00	10:00	1
Jane	A	09:00:00	10:30:00	9:00	1
Jane	A	09:00:00	10:30:00	9:30	1
Jane	A	09:00:00	10:30:00	10:00	1
Catherine	C	09:30:00	11:00:00	9:30	1
Catherine	C	09:30:00	11:00:00	10:00	1
Lana	A	08:30:00	11:30:00	8:30	1
Lana	A	08:30:00	11:30:00	9:00	1
Lana	A	08:30:00	11:30:00	9:30	1
Lana	A	08:30:00	11:30:00	10:00	1

the unpivioted data can then be added to a matrixs with interval as the colunm 

and a formula made for the totals inside the matrixs and max for the totals

myMax =  maxx(VALUES('table'[Column ID]),CALCULATE(max('table'[Value])))