I understand that MAX only works on a column. Normally I would create a column using my measure and then apply MAX. However in this case my measure is too multidimensional to do this. I have a number of stores and i am looking at till efficiency. So i am calculating how many transactions per hour each till at each store for each hour of the day is doing. To do this i use
Where each transaction has a code "StoreDayHour" = store no+date+hour no
This generates something like this for a single store for a single hour (9). There are 2 tills
[TABLE="width: 347"]
<colgroup><col><col span="3"></colgroup><tbody>[TR]
[TD]Trans per hour per store[/TD]
[TD]Time of day[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD]9 Total[/TD]
[/TR]
[TR]
[TD]Location[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Monument[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]74[/TD]
[/TR]
[TR]
[TD]01/05/2015[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]73[/TD]
[/TR]
[TR]
[TD]05/05/2015[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]85[/TD]
[/TR]
[TR]
[TD]06/05/2015[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]82[/TD]
[/TR]
[TR]
[TD]07/05/2015[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]66[/TD]
[/TR]
[TR]
[TD]08/05/2015[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]73[/TD]
[/TR]
[TR]
[TD]11/05/2015[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]66[/TD]
[/TR]
[TR]
[TD]12/05/2015[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]75[/TD]
[/TR]
[TR]
[TD]13/05/2015[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]73[/TD]
[/TR]
[TR]
[TD]14/05/2015[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]72[/TD]
[/TR]
[TR]
[TD]15/05/2015[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]73[/TD]
[/TR]
[TR]
[TD]18/05/2015[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]78[/TD]
[/TR]
[TR]
[TD]19/05/2015[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]79[/TD]
[/TR]
[TR]
[TD]20/05/2015[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]66[/TD]
[/TR]
[TR]
[TD]21/05/2015[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]76[/TD]
[/TR]
[TR]
[TD]22/05/2015[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]68[/TD]
[/TR]
[TR]
[TD]26/05/2015[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]73[/TD]
[/TR]
[TR]
[TD]27/05/2015[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]67[/TD]
[/TR]
[TR]
[TD]28/05/2015[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]79[/TD]
[/TR]
[TR]
[TD]29/05/2015[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]78[/TD]
[/TR]
[TR]
[TD]Max[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]85[/TD]
[/TR]
</tbody>[/TABLE]
If I remove the day rows i just get an average for the store over all days which is 42 for Till 1. what I really want to know is the maximum that can be put through the till which is the 58 I calc in excel from 5/5. I'm making an assumption if you can do 58 transaction on any one till on any day at any hour then you should be able to achieve that on any day (or say 90% of it) .
So i'm looking for a measure that gives Max Trans/hour of the individual storedayhour slots that i can group/slice by stores,hours, days of the week . The only way I can think of doing it creating a massive table of every storedayhour then use the MAX function but hoping there is an elegant way to do this?
Thanks
Mike
Code:
[Transaction Count]/ DISTINCTCOUNT(
Transactions[StoreDayHourID]
)
Where each transaction has a code "StoreDayHour" = store no+date+hour no
This generates something like this for a single store for a single hour (9). There are 2 tills
[TABLE="width: 347"]
<colgroup><col><col span="3"></colgroup><tbody>[TR]
[TD]Trans per hour per store[/TD]
[TD]Time of day[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD]9 Total[/TD]
[/TR]
[TR]
[TD]Location[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Monument[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]74[/TD]
[/TR]
[TR]
[TD]01/05/2015[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]73[/TD]
[/TR]
[TR]
[TD]05/05/2015[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]85[/TD]
[/TR]
[TR]
[TD]06/05/2015[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]82[/TD]
[/TR]
[TR]
[TD]07/05/2015[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]66[/TD]
[/TR]
[TR]
[TD]08/05/2015[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]73[/TD]
[/TR]
[TR]
[TD]11/05/2015[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]66[/TD]
[/TR]
[TR]
[TD]12/05/2015[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]75[/TD]
[/TR]
[TR]
[TD]13/05/2015[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]73[/TD]
[/TR]
[TR]
[TD]14/05/2015[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]72[/TD]
[/TR]
[TR]
[TD]15/05/2015[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]73[/TD]
[/TR]
[TR]
[TD]18/05/2015[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]78[/TD]
[/TR]
[TR]
[TD]19/05/2015[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]79[/TD]
[/TR]
[TR]
[TD]20/05/2015[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]66[/TD]
[/TR]
[TR]
[TD]21/05/2015[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]76[/TD]
[/TR]
[TR]
[TD]22/05/2015[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]68[/TD]
[/TR]
[TR]
[TD]26/05/2015[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]73[/TD]
[/TR]
[TR]
[TD]27/05/2015[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]67[/TD]
[/TR]
[TR]
[TD]28/05/2015[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]79[/TD]
[/TR]
[TR]
[TD]29/05/2015[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]78[/TD]
[/TR]
[TR]
[TD]Max[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]85[/TD]
[/TR]
</tbody>[/TABLE]
If I remove the day rows i just get an average for the store over all days which is 42 for Till 1. what I really want to know is the maximum that can be put through the till which is the 58 I calc in excel from 5/5. I'm making an assumption if you can do 58 transaction on any one till on any day at any hour then you should be able to achieve that on any day (or say 90% of it) .
So i'm looking for a measure that gives Max Trans/hour of the individual storedayhour slots that i can group/slice by stores,hours, days of the week . The only way I can think of doing it creating a massive table of every storedayhour then use the MAX function but hoping there is an elegant way to do this?
Thanks
Mike