# Working out Maximum of a Measure

#### masplin

##### Active Member
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

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

 Trans per hour per store Time of day 9 9 Total Location 1 2 Monument 42 31 74 01/05/2015 43 30 73 05/05/2015 58 27 85 06/05/2015 52 30 82 07/05/2015 41 25 66 08/05/2015 42 31 73 11/05/2015 41 25 66 12/05/2015 31 44 75 13/05/2015 45 28 73 14/05/2015 38 34 72 15/05/2015 41 32 73 18/05/2015 43 35 78 19/05/2015 48 31 79 20/05/2015 44 22 66 21/05/2015 45 31 76 22/05/2015 42 26 68 26/05/2015 34 39 73 27/05/2015 34 33 67 28/05/2015 49 30 79 29/05/2015 35 43 78 Max 58 44 85

<colgroup><col><col span="3"></colgroup><tbody>
</tbody>

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

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
You can use GENERATE or CROSSJOIN to create the Cartesian product of all the dimensions you want to run the max over, then pass that and the "transactions per hour" measure to a MAXX function.
You could probably also use nested MAXXs. Not sure about performance.

For example, if the dimensions were Store, Day, Hour and Till, then a possible measure using CROSSJOIN would be:
Code:
``````Maximum Trans per hour :=
MAXX (
CROSSJOIN (
VALUES ( [Store] ),
VALUES ( [Day] ),
VALUES ( [Hour] ),
VALUES ( [Till] )
),
[Trans per hour]
)``````

Great never come across CROSSJOIN. So have I understood correctly that this is creating a table with every combination of Store, Day, Hour and Till and calculating [Trans per hour] for each possibility and then taking the MAX? Presumably this table shrinks if you apply filter/slicer to say stores so you only get the max for those stores selected. Giving it a go thanks

Yes that's right, in this example CROSSJOIN would create a 4-column table with all combinations of those columns, but filtered by the current filter context. MAXX then evaluates the measure in the context of each row and takes the max like you said.

Your the best what a brilliant formula. Works perfectly so going to find lots of uses for that.

Cheers
Mike

**** Oz, that is hot!

Just light commentary on "how do I find the max value returned from a measure?" -- using MAXX is probably always your answer.

SomeMax := MAXX(SomeTable, [SomeMeasure])

Typically this is going to look something like...

MaxPerDay := MAXX(VALUES(Calendar[DayNum]), [SomeMeasure])

Which is going to evaluate [SomeMeasure] for each Calendar[DayNum] in the current context (you could use ALL() instead of VALUES() to do the whole table, instead of just current context).

Oz took that concept and turned it into some ninja level "Eval this for every combo of { Store, Day, Hour, Till }, without requiring an artificial id fields"... which is **** cool.

Well this does exactly what it says on the tin until I tried to push it too far!!!! i started out doing hourly time slicers and was taking hours to calculate. When I tried using 15 minute slicers I get "XML for Analysis timed out ...". So the question is can I limit the amount of data going into the calculation?

We have 20 stores but actually this calculation is pretty much store specific so i am using a slicer to just select one store. I'm not sure whether the slicer acts before or after the calculation i.e. is it calculating the data for every store and then just filtering the results. If so is there a way to filter for the store shown in the slicer BEFORE doing the calculation?

Mike

Pretty sure the answer is yes: If you start out with an unfiltered Store slicer and add the Max measure to the PivotTable before selecting a Store on the slicer, the calculation will be performed for every store.

One solution to your immediate problem could be to wrap your measure in a condition so that it only evaluates if exactly one store is selected in the entire PivotTable.

I'm thinking of:
Code:
``````Maximum Trans per Hour (single store only):=
IF (
COUNTROWS ( ALLSELECTED ( [Store] ) ) = 1,
[B][I]    < Original measure >[/I][/B]
)``````

This measure should only perform the original calculations when a single Store is selected on the slicer, and return blank otherwise.

I would also turn off Grand Totals in the PivotTable in advance so that the calculation for the single Store isn't repeated for the grand total.

Other ideas...

Not sure if there is possible gain in efficiency by using GENERATE instead of CROSSJOIN?
CROSSJOIN will be creating every possible combination that exists in the current filter context, even for some combinations that don't exist in your fact table.
For example, if Store A has 2 tills and Store B has 4 tills, CROSSJOIN will generate 8 combinations, while only 6 combinations exist in reality.
Someone else may know more about using GENERATE in this sort of situation...

I do have the slicer selected before adding the values so you think that this is already filtering the data before doing the calculation? If so then this isn't going to gain anything unfortunately.

Hmm, I wonder how many rows the CROSSJOIN is producing.

Out of interest, what does this measure give you for the tables you are crossjoining (assuming it calculates in a reasonable time) e.g.
Code:
``````NumRows :=
COUNTROWS(
CROSSJOIN (
VALUES ( [Store] ),
VALUES ( [Day] ),
VALUES ( [Hour] ),
VALUES ( [Till] )
)
)``````

Replies
2
Views
141
Replies
3
Views
127
Replies
1
Views
646
Replies
3
Views
207
Replies
1
Views
444

Threads
1,203,075
Messages
6,053,393
Members
444,661
Latest member
liamoohay

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back