Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Ranking most common codes from two columns

I need some help with this issue.

 

I have a SharePoint list containing three columns. First column has an agent name, second has a response code that corresponds with an action the agent needs to take and the third is a location. I want to rank the two most common codes from both columns from the last 30 days. In this example, two most common codes are CHA.1 and LA.1

 

NAME:              RESPONSE CODE:                LOCATION CODE:

John                  CHA.1.                                 LA.1

Henry.               GH.2.                                   NYC.1

Martha.             CHA.1.                                 LA.1

 

Ideally I would like to show the two most common codes from the previous month and from the current month to date, if possible.

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    Anonymous Maybe:

    Measure 2 = 
    VAR __Table = SUMMARIZE('Table3',[LOCATION CODE:],"__Count",COUNTROWS('Table3'))
    VAR __Table1 = SUMMARIZE('Table3',[RESPONSE CODE:],"__Count",COUNTROWS('Table3'))
    VAR __MaxLocCount = MAXX(__Table,[__Count])
    VAR __MaxRepCount = MAXX(__Table,[__Count])
RETURN
    MAXX(FILTER(__Table1,[__Count] = __MaxRepCount),[RESPONSE CODE:]) & " and " & MAXX(FILTER(__Table,[__Count] = __MaxLocCount),[LOCATION CODE:])

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Anonymous Maybe:

    Measure 2 = 
    VAR __Table = SUMMARIZE('Table3',[LOCATION CODE:],"__Count",COUNTROWS('Table3'))
    VAR __Table1 = SUMMARIZE('Table3',[RESPONSE CODE:],"__Count",COUNTROWS('Table3'))
    VAR __MaxLocCount = MAXX(__Table,[__Count])
    VAR __MaxRepCount = MAXX(__Table,[__Count])
RETURN
    MAXX(FILTER(__Table1,[__Count] = __MaxRepCount),[RESPONSE CODE:]) & " and " & MAXX(FILTER(__Table,[__Count] = __MaxLocCount),[LOCATION CODE:])
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Greg_Deckler This does work beautifully.

     

    The only one issue is that I'm trying to rank them according to last month and month to date. Is there something we can add to the formula for that? 

    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      3 years ago

      Anonymous What do you have in the way of a Date column? You can get your dates like:

      VAR __EndDate = TODAY()
VAR __StartDate = DATE(YEAR(__EndDate),MONTH(__EndDate),1)
// these would be for mtd
VAR __EndDate = EOMONTH(TODAY(),-1)
VAR __StartDate = DATE(YEAR(__EndDate),MONTH(__EndDate),1)
// these would be for previous month

// You can then use these within your SUMMARIZE like this:
SUMMARIZE(FILTER('Table',[Date] >= __StartDate && [Date]<=__EndDate),....)
      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Both of these worked great! Thanks Greg! 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Greg_Deckler Since you responded to this, the formula has worked great for ranking the most common codes from the previous month. But I am having a hard time incorporating this formula into my dashboard for the purpose of ranking month to date. Can you tell me where I'm going wrong? 

     

    This is what I have:

    Measure = 

    VAR __EndDate = Today() 

    VAR __Start Date = DATE(YEAR(__End Date), Month(__End Date), 1)

    VAR __Table = SUMMARIZE('Table1', 'Table1'[RESPONSE CODE], "__Count", COUNTROWS ('Table1')) 

    VAR __Table1 = SUMMARIZE('Table1' [LOCATION CODE], "__Count", COUNTROWS('Table1')) 

    VAR __MaxLocCount=MAXX(__Table, [__Count]) 

    VAR __MaxRepCount=MAXX(__Table, [__Count]) 

    RETURN

    MAXX(Filter(__Table1, [__Count] = __MaxRepCount), [LOCATION CODE]) & MAXX(Filter(__Table, [__COUNT] = __MaxLocCount), [RESPONSE CODE])