Forum Discussion

EnrichedUser's avatar
EnrichedUser
Icon for Helper III rankHelper III
5 years ago
Solved

Distinct Count of User by Action with Minimum Threshold

My goal is provide the distinct count of users who performed a specifc action (1 action = 1 record under TransactionType) more than a threshold. 

 

I have a table called ItemLedger. There are several columns but the ones of importance are:

BranchID, UserID, TransactionType, LedgerDate

BranchIDUserIDTransactionTypeLedgerDate
CL20RBYRDORD.PICK2/24/2021
CL20RBYRDITEM.MOVE2/24/2021
CL20RBYRDITEM.MOVE2/24/2021
CL20DSMITHORD.PICK2/24/2021
CL20DSMITHORD.PICK2/24/2021
CL20DSMITHORD.SHIP2/24/2021
CL21DSMITHORD.SHIP2/24/2021
CL20DSMITHORD.PICK2/24/2021
CL20DSMITHORD.PICK2/24/2021
CL20DSMITHORD.PICK2/24/2021
CL20RBYRDORD.PICK2/24/2021
CL20RBYRDORD.PICK2/24/2021
CL20RBYRDORD.PICK2/24/2021
CL20RBYRDORD.PICK2/24/2021
CL20RBYRDORD.PICK2/24/2021
CL20DSMITHORD.PICK2/23/2021
CL20DSMITHORD.PICK2/23/2021
CL20DSMITHORD.PICK2/23/2021
CL20DSMITHORD.PICK2/23/2021
CL20DSMITHORD.PICK2/23/2021
CL20DSMITHITEM.MOVE2/23/2021
CL20DSMITHORD.PICK2/23/2021
CL20DSMITHORD.PICK2/23/2021
CL20DSMITHORD.PICK2/23/2021
CL20DSMITHITEM.MOVE2/23/2021
CL20DSMITHORD.PICK2/23/2021
CL20DSMITHORD.PICK2/23/2021
CL20DSMITHORD.PICK2/23/2021
CL20RBYRDITEM.MOVE2/23/2021
CL20RBYRDORD.PICK2/23/2021

 

My goal is provide the distinct count of users who performed a specifc action more than a threshold. 

 

Using a specific example based on the above sample, I would like to determine how many users picked more than 4 times for each day. 

 

Intermediate Table Example:

LedgerDateUserIDCount of ORD.PICKThreshold
2/24/2021RBYRD64
2/24/2021DSMITH54
2/23/2021RBYRD14
2/23/2021DSMITH114

 

Expected Output:

 

LedgerDateDistinct Count of Users above threshold for ORD.PICK
2/24/20212
2/23/20211

 

 

Notes:

 

Pickers =
CALCULATE(DISTINCTCOUNT(ItemLedger[UserID]), ItemLedger[TransactionType] = "ORD.PICK")
 
was able to tell me how users performed the action, but leaves out a minimum threshold.
 
I was trying to use summarizecolumns to do the grouping but I lost the row context and wasnt able to see it by date. 
 
I also tried using just summarize

GroupedTable =
SUMMARIZECOLUMNS(ItemLedger[UserID],ItemLedger[TransactionType],'Date'[Date],
FILTER(ALL(ItemLedger), ItemLedger[TransactionType] = "ORD.PICK"),
"Picks", [Lines Picked])
Need Help
  • EnrichedUser's avatar
    EnrichedUser
    5 years ago

    Solution:

     

    Pickers =
    VAR Table1 =
    SUMMARIZE(ItemLedger,ItemLedger[UserID], 'Date'[Date], "Daily Picks", CALCULATE(COUNTROWS(ItemLedger), FILTER(ItemLedger, ItemLedger[TransactionType] = "ORD.PICK")),
    "Did Pick", CALCULATE(DISTINCTCOUNT(ItemLedger[UserID]),FILTER(ItemLedger, ItemLedger[TransactionType] = "ORD.PICK")))
    RETURN

    SUMX(FILTER(Table1, [Daily Picks] > 15),[Did Pick])
     
     
    The variable table allowed me to reference the calculated fields of "Did Pick" and "Daily Picks" in SUMX

    I could likely simplify using only one filter. 
     
    The threshold used in this example is > 15

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    EnrichedUser , Create a measures like


    Sumx(Summarize(ItemLedger,ItemLedger[UserID],'Date'[Date],
    "_1",CALCULATE(DISTINCTCOUNT(ItemLedger[UserID]), filter(ItemLedger,ItemLedger[TransactionType] = "ORD.PICK"))), [_1])

     

    infact remove filter and use slicer to control type

     

    • EnrichedUser's avatar
      EnrichedUser
      Icon for Helper III rankHelper III
      5 years ago

      Thank you so much for getting back to me so quickly! Unfortutaly, this solution did not include the the threshold pieces what I needed the help with. However, it did point me to focus more on trying to solve with sumx.

  • EnrichedUser's avatar
    EnrichedUser
    Icon for Helper III rankHelper III
    5 years ago

    Solution:

     

    Pickers =
    VAR Table1 =
    SUMMARIZE(ItemLedger,ItemLedger[UserID], 'Date'[Date], "Daily Picks", CALCULATE(COUNTROWS(ItemLedger), FILTER(ItemLedger, ItemLedger[TransactionType] = "ORD.PICK")),
    "Did Pick", CALCULATE(DISTINCTCOUNT(ItemLedger[UserID]),FILTER(ItemLedger, ItemLedger[TransactionType] = "ORD.PICK")))
    RETURN

    SUMX(FILTER(Table1, [Daily Picks] > 15),[Did Pick])
     
     
    The variable table allowed me to reference the calculated fields of "Did Pick" and "Daily Picks" in SUMX

    I could likely simplify using only one filter. 
     
    The threshold used in this example is > 15