Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Need help with summing max function

Hello,    I am in need of some help. I am trying to get the max amount of words per month by a client. I have used the following formula to do so   MaxWords (M) = CALCULATE(MAX(Query1[POWordCoun...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi Anonymous

    Create a index column from the Query Editor

     

    Create calculated columns

    max = CALCULATE(MAX([words]),ALLEXCEPT(Sheet1,Sheet1[client],Sheet1[YearMonth]))

count =
CALCULATE (
    COUNT ( Sheet1[max] ),
    FILTER (
        ALLEXCEPT ( Sheet1, Sheet1[client], Sheet1[YearMonth] ),
        EARLIER ( Sheet1[words] ) = [words]
    )
)

final output =
    IF (
     [words] = [max],
     IF (
     [count] > 1,
     IF ( Sheet1[Index] = MIN ( Sheet1[Index] ), Sheet1[max] ),
     [max]
     )
    )

     

    Best Reagrds

    Maggie

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Ashish_Mathur 

 

Thank you for your reply. As I am working with your formula I was still getting the same outcome I had previously. It is still duplicating the max amount. 

 

I have a client, Client A. 

 

Here is his data. 

 

clientYearMonthwordsMaxWords
Person A2018/1           7,000                         7,000
Person A2018/5         12,000                       12,000
Person A

2018/1

           6,500                         0
Person A2018/3           4,000                         4,000

 

However, when I use the formula you provided me I am getting this as my outcome.

 

clientYearMonthwordsMaxWords
Person A2018/1           7,000                         7,000
Person A2018/5         12,000                       12,000
Person A2018/1           6,500                         7,000
Person A2018/3           4,000                         4,000

 

Any other insight you may have??

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    It worked perfectly for me.  I cannot say what mistake you are committing.  Retry please.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      You are right, I was referencing the wrong column when using your formula. I have it working now, thanks!

       

      One other questions that I came across after using your formula. I see that I have a client with the same max words twice. It is causing your formula to pull both of those numbers as the max. I am wondering if there is a way to only keep one of those numbers. 

       

      Example data:

       

         Desired Outcome
      clientYearMonthwordsMaxWords
      Person A2018/1           7,000                         7,000
      Person A2018/5         12,000                       12,000
      Person A2018/1           6,500                                -  
      Person A2018/3           6,600                         6,600
      Person A2018/1           7,000                         7,000
      Person A2018/8           6,000                         6,000
      Person A2018/3           6,600                         6,600

       

      As you can see the formula works when the word amounts are different, but when you run into two instances where they have the same max for words it will duplicate it. Is there a way to fix it? Thank you again for your help!

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        7 years ago

        Hi,

         

        It may be possible to solve this using an Index column in the Query Editor.  It will take time to solve this.  Hope someone else helps you with this.