locked
Dynamic Mode Helper Column (most frequent category for various filter contexts) RRS feed

  • Question

  • Background:

    I am in a distribution and logistics environment. We have pickers that get material from the warehouse and physically move it to shipping. There is a transaction that documents what, where, when, and who picked the item (and which customer order/line its related to). The pickers can work in multiple areas of the warehouse each day (this is the 'where'). I need a dynamic way (subject to filter context) to see the area or 'where' they did the most picks (mode).

    For example:

    • Last week, Bobby      Joe predominantly picked in the small area.
    • Yesterday, Suzie      Queue predominantly picked in the large area.

    I have reviewed and tried the following suggestions (which were compiled on these forums), but they only help me flag the mode and they aren't dynamic with filter context.

    • http://www.mrexcel.com/forum/powerpivot-questions/713060-how-write-code-mode-function-powerpivot.html
    • http://blogs.msdn.com/b/frankie_yuen/archive/2010/08/18/powerpivot-create-custom-median-amp-mode-functions.aspx
    • http://social.technet.microsoft.com/Forums/office/en-US/87bc8b04-6cd7-4b56-8b39-08eb9ef5489f/using-powerpivot-and-dax-to-determine-the-most-common-product-purchase-mode-function

    I am using Excel Power Pivot 2013.

    Diagram View:

    For the purpose of this question. There is a transaction table "MaterialTxnDate" and a Calendar table "tCal" that I am using to do groupings in my reports. The are related by the "Day Hour" field. In the "MaterialTxnDate" table, "Day Hour" is a calculated field which rounds the time stamp of the pick down to the nearest hour. I never need to group more granular than an hour.

    Key Fields for this question:

    • MaterialTxnDate[Day Hour] this is the 'when' the pick occurred
    • MaterialTxnDate[mtdPickerName]      name of the person 'who'
    • MaterialTxnDate[Adj Sub] is the area the pick was from 'where'

    Dummy Data and Desired Results:

    Picker Mode is the calculated column I need that responds to the example filter contexts:

    Day   Hour

    mtdPickerName

    Adj   Sub

    Picker Mode (no filters)

    Picker Mode (Filtered/Grouped by   picker name)

    Picker Mode (Filtered/Grouped by   Picker and Day)

    11/30/2017 6:00

    Bobby   Joe

    Large

    Large

    Large

    Large

    11/30/2017 8:00

    Bobby   Joe

    Large

    Large

    Large

    Large

    11/30/2017 9:00

    Bobby   Joe

    Small

    Large

    Large

    Large

    11/29/2017 6:00

    Bobby   Joe

    Large

    Large

    Large

    Large

    11/29/2017 8:00

    Bobby   Joe

    Large

    Large

    Large

    Large

    11/29/2017 9:00

    Bobby   Joe

    Large

    Large

    Large

    Large

    11/29/2017 10:00

    Bobby   Joe

    Large

    Large

    Large

    Large

    11/30/2017 6:00

    Suzie   Que

    Small

    Large

    Small

    Small

    11/30/2017 8:00

    Suzie   Que

    Small

    Large

    Small

    Small

    11/30/2017 9:00

    Suzie   Que

    Small

    Large

    Small

    Small

    11/29/2017 6:00

    Suzie   Que

    Large

    Large

    Small

    Large

    11/29/2017 8:00

    Suzie   Que

    Small

    Large

    Small

    Large

    11/29/2017 9:00

    Suzie   Que

    Large

    Large

    Small

    Large

    11/29/2017 10:00

    Suzie   Que

    Large

    Large

    Small

    Large

    Dax Currently used:

    Calculated Column to count the frequency picked in each area (currently doesn't respond to filter context) I can take a MAX() of this to get a flag on the mode, but I need the column to return the text string of the mode.

    Sub Count =CALCULATE(

                         COUNTA([Adj Sub]),

                         FILTER(MaterialTxnDate,MaterialTxnDate[Adj Sub]=EARLIER(MaterialTxnDate[Adj Sub])),

                         ALLEXCEPT(MaterialTxnDate,MaterialTxnDate[mtdPickerName])

                          )

    Also tried a measure, but I can't use it to group data (obviously)

    Picker Mode:=IF(

                    CALCULATE(

                              MAXX(

                                   VALUES(MaterialTxnDate[Adj Sub]),

                                   CALCULATE(

                                             COUNTROWS(MaterialTxnDate)

                                             )

                                   ),

                              ALLSELECTED(MaterialTxnDate)

                              )

                              =COUNTROWS(MaterialTxnDate),

                     "Mode",

                     BLANK()

                    )


    • Edited by Lud1van Thursday, November 30, 2017 2:11 PM formatting cleanup
    Thursday, November 30, 2017 2:07 PM

Answers

  • You can get the mode using the TOPN function with a parameter of 1. The following 2 expressions will get the overall mode and the mode per picker and you just have to vary the filter expression inside the calculatetable() call to get other types of modes (such as the most frequent location for a given hour). Not I also defined a [TxnCount] measure instead of repeatedly calling CALCULATE(COUNTROWS(MaterialTxnDate)).

    eg

    TxnCount:=COUNTROWS(MaterialTxnDate)

    Overall Mode:=CONCATENATEX(
      calculatetable(
        TOPN(1, ALL(MaterialTxnDate[Adj Sub]),[TxnCount])
        ,ALL(MaterialTxnDate) /* this controls the scope of the TOPN */
        )
    , MaterialTxnDate[adj sub])

    Picker Mode:=CONCATENATEX(
      calculatetable(
        TOPN(1, ALL(MaterialTxnDate[Adj Sub]),[TxnCount])
        ,ALLEXCEPT(MaterialTxnDate, MaterialTxnDate[mtdPickerName) /* this controls the scope of the TOPN */
        )
    , MaterialTxnDate[adj sub])


    http://darren.gosbell.com - please mark correct answers

    Friday, December 1, 2017 2:41 AM

All replies

  • You can get the mode using the TOPN function with a parameter of 1. The following 2 expressions will get the overall mode and the mode per picker and you just have to vary the filter expression inside the calculatetable() call to get other types of modes (such as the most frequent location for a given hour). Not I also defined a [TxnCount] measure instead of repeatedly calling CALCULATE(COUNTROWS(MaterialTxnDate)).

    eg

    TxnCount:=COUNTROWS(MaterialTxnDate)

    Overall Mode:=CONCATENATEX(
      calculatetable(
        TOPN(1, ALL(MaterialTxnDate[Adj Sub]),[TxnCount])
        ,ALL(MaterialTxnDate) /* this controls the scope of the TOPN */
        )
    , MaterialTxnDate[adj sub])

    Picker Mode:=CONCATENATEX(
      calculatetable(
        TOPN(1, ALL(MaterialTxnDate[Adj Sub]),[TxnCount])
        ,ALLEXCEPT(MaterialTxnDate, MaterialTxnDate[mtdPickerName) /* this controls the scope of the TOPN */
        )
    , MaterialTxnDate[adj sub])


    http://darren.gosbell.com - please mark correct answers

    Friday, December 1, 2017 2:41 AM
  • Thanks for the reply. I can get this working smoothly as a measure, but measures cannot be used as filter criteria in rows/columns of a pivot table. I need a calculated column that does this determination. I've tried tweaking the logic but a myriad of error. Also, power pivot 2013 doesn't have concatenatex().Thoughts?

    Tuesday, December 5, 2017 10:10 PM
  • Excel 2016 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
    Generated "Most Frequented Area" as a Calculated Column.
    Used PQ instead of DAX.
    There are probably oodles of easier ways to get to your unspecified, ultimate goal.
    Share files like these:
    http://www.mediafire.com/file/aw01f8itx0w0mp1/12_05_17b.xlsx
    http://www.mediafire.com/file/wg36anis3suus22/12_05_17b.pdf

    Wednesday, December 6, 2017 12:38 AM
  • Thanks for the reply. I can get this working smoothly as a measure, but measures cannot be used as filter criteria in rows/columns of a pivot table. I need a calculated column that does this determination. 

    You could create a calculated column that should work in Excel 2013 with the following:

    = filter(all(Table1[Adj Sub]), Table1[Adj Sub] =
      calculatetable(
        TOPN(1, ALL(Table1[Adj Sub]),[TxnCount])
        ,ALLEXCEPT(Table1, Table1[mtdPickerName] /* this controls the scope of the TOPN */
        )))

    But your original requirement explicitly said "I need a dynamic way (subject to filter context)" which conflicts with your requirement to use a calc column as only measures are evaluated in filter context. Calculated columns are calculated once at processing time then stored. You cannot have a filter that changes in response to other filters changing.

    What sort of output are you trying to achieve? There may be other approaches (such as using a disconnected table) that might be able to achieve the output your require.



    http://darren.gosbell.com - please mark correct answers

    Wednesday, December 6, 2017 1:17 AM
  • Daren,

    I now understand that a calculated column will not be "dynamic" in the way that I wanted. Thank you for the explanation. The downside about making this a measure is that I can't use a measure as a row or column in a pivot table. With that in mind, it will be best for me to make a calculated column for each of the permutations of filters that I am concerned with (at this point its only two so its manageable).

    Here's an example of the output that I'm working towards:

    Yesterday   Flag

    1

    Current     Day Flag

    All

    Which     Line

    All

    Shift   Mode

    1st

    Row   Labels

    #Picks

    Working Time [hr]

    Picks/Hr

    %ofRate

    SP   Cherry

    Picker   Name 1

    333

    7.6

    44.0

    183 %

    Picker   Name 2

    296

    6.8

    43.4

    181   %

    Picker   Name 3

    265

    6.9

    38.4

    160 %

    Picker   Name 4

    253

    6.7

    37.6

    157   %

    This shows each pickers' rates compared to the expected rate in the area where they picked the most (pick mode) on the current day or previous day.

    I am also using the same logic that you provided to determine in which shift the picker worked the most. I'm still running into some hiccups.

    Measures:

    TxnCnt:=COUNTROWS(MaterialTxnDate)

    Calculated Columns:

    Picker Mode =CONCATENATE(

                             CALCULATETABLE(

                                            TOPN(1,ALL(MaterialTxnDate[Adj Sub]),[TxnCnt]),

                                            ALLEXCEPT(MaterialTxnDate,MaterialTxnDate[mtdPickerName])

                                            ),

                             BLANK()

                             )

    Shift Mode =CONCATENATE(

                            CALCULATETABLE(

                                           TOPN(1,ALL(tCal[Shift]),[TxnCnt]),

                                           ALLEXCEPT(MaterialTxnDate,MaterialTxnDate[mtdPickerName],tCal[Date])

                                           ),

                            BLANK()

                            )

    This shift mode calculated column is working without issue.

    The picker mode calculated column works sometimes and other times it gives me the following error:

    “Calculation error in column 'MaterialTxnDate'[]: A table of multiple values was supplied where a single value was expected.”

    Whenever I change the column inside the ALLEXCEPT() which controls the TOPN(), everything works except using [mtdPickerName]. After doing some research, the 'first error' is occurring on a picker that only has 2 records in the time fence. If there is a tie, in order_by values, at the N-th row of the table, then all tied rows are returned. Then, when there are ties at the N-th row the function might return more than n rows. When I remove these records from my data, everything works! So I replaced CONCATENATE() with FIRSTNONBLANK() to solve the issue. Here's what I have now:

    =FIRSTNONBLANK(
                                      CALCULATETABLE(
                                                                       TOPN(1,ALL(MaterialTxnDate[Adj Sub]),[TxnCnt]),
                                                                       ALLEXCEPT(MaterialTxnDate,MaterialTxnDate[mtdPickerName])
                                                                       ),
                                      MaterialTxnDate[Adj Sub]
                                      )



    • Edited by Lud1van Wednesday, December 13, 2017 4:26 PM
    Wednesday, December 13, 2017 2:22 PM