Working out Maximum of a Measure

[masplin]

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

[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

 

[masplin]

Well shockingly the answer is 3,036,000!!!! So you can image when I did it in 15min slots its exploded!!!!

However it sounds as if you are saying if you select the slicers first then the crossjoin should be much smaller? I added the numrow to a pivot with one store for 3 months and its says 6710 but it take hours to calculate this so now sure this isn't just a filtered calc on the whole lot.

does GENERATE Do something different?

 

[Ozeroth]

Actually after some thinking, CROSSJOIN or GENERATE could be overkill, and SUMMARIZE might do the trick better.
(The only reason I was thinking of GENERATE is because it evaluates the second table in the context of the rows from the first table.)

SUMMARIZE can be used to generate only the combinations that actually exist in your transaction data, which should make the table passed to MAXX as small as possible.

Here is a possible measure. I'm assuming 'Transactions' is your fact table and Table1..4 are lookup tables containing the Store etc dimensions, but they could equally come from Transactions if there aren't lookup tables defined (however I think lookup tables would be most efficient).

Maximum Trans per hour :=
MAXX (
    SUMMARIZE (
        Transactions,
        Table1[Store],
        Table2[Day],
        Table3[Hour],
        Table4[Till]
    ),
    [Trans per hour]
)

Depending how this goes, would you be able to post a model (with dummy data if necessary) but similar number of rows to your actual data, with the measures set up?

 

[masplin]

Well this is very interesting. Using measure designed around the SUMMARIZE function are twice as fast as the CROSSJOIN version.

for amusement what would the syntax be to use GENERATE as be nice to have a comparison of the 3 different methods?

Mike

 

[Ozeroth]

Hmm, after experimenting I can't get GENERATE to do anything different from CROSSJOIN. Maybe someone can come up with something?

It could well be the Transactions per Hour measure itself that is causing the slow calculation. What does that measure look like now?

 

[masplin]

Sorry delay in replying but been on another project. The whole thing is much faster, but i suspect this was because of the AVERAGEX function we were discussing on the other thread being extremely slow. Combination of 2 shody bits of code producing rubbish!!! Putting SUMMARIZE into the AVERAGEX instead of using EARLIER seemed to make a huge difference.

I am now wondering if you should use SUMMARIZE instead of EARLIER in all calculations. Aren't they doing the same thing i.e. creating a temporary table where a field matches other files in the database? I quite often have 2 or 3 earlier functions which a calculation. Maybe I have misunderstood SUMMARIZE?

Mike

 

[Ozeroth]

SUMMARIZE itself can be used to extract all existing combinations of specific columns from a table (including related tables) which is what the previous post was doing.

EARLIER is used to refer to a column's value in the row context outside the current one. If you're using EARLIER in a measure, you must be using one iterator ("X" or FILTER function) inside another which could be slow depending on table size.

Not or sure about general rules, but I would try to avoid nesting iterators unless there's no other way.