Working out Maximum of a Measure

masplin

Active Member
Joined
May 10, 2010
Messages
413
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

[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
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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]
)
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Your the best what a brilliant formula. Works perfectly so going to find lots of uses for that.

Cheers
Mike
 
Upvote 0
**** 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.
 
Upvote 0
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
 
Upvote 0
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...
 
Upvote 0
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.
 
Upvote 0
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] )
    )
)
 
Upvote 0

Forum statistics

Threads
1,224,113
Messages
6,176,448
Members
452,728
Latest member
mihael546

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

Which adblocker are you using?

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
Back
Top