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[POWordCount]),
FILTER(query1,Query1[PurchaseOrderVendor] = EARLIER(Query1[PurchaseOrderVendor])),
FILTER(Query1,Query1[YearMonth] = EARLIER(Query1[YearMonth])))
 
I am now looking to take that formula and use it in a gauge visual. However, when I take the column it is giving me duplicates for each month so I cannot just sum the column to get my desired outcome. 
 
I am fairly new to power BI and do not know a ton of DAX. I would appreciate any help I can receive from all your experts out there! Thanks. 
 
Here is some sample data:
   Desired Outcome
clientYearMonthwordsMaxWords
Person A2018/1           7,000                         7,000
Person A2018/5         12,000                       12,000
Person A2018/4           6,500                         6,500
Person A2018/3           4,000                         4,000
Person B2018/1         23,000                       23,000
Person B2018/8           6,000                         6,000
Person B2018/1           6,6000
Person B2018/2           3,300                         3,300
Person B2018/5         11,000                       11,000
Person C2018/4           8,6000
Person C2018/10           4,5000
Person C2018/10           6,200                         6,200
Person C2018/6           5,500                         5,500
Person C2018/4           9,000                         9,000
Person C2018/2         15,000                       15,000

 

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

7 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    Hi,

     

    This calculated column formula will work

     

    =if([words]=CALCULATE(MAX(Table1[words]),FILTER(Table1,Table1[client]=EARLIER(Table1[client])&&Table1[YearMonth]=EARLIER(Table1[YearMonth]))),CALCULATE(MAX(Table1[words]),FILTER(Table1,Table1[client]=EARLIER(Table1[client])&&Table1[YearMonth]=EARLIER(Table1[YearMonth]))),0)

     

    Hope this helps.

  • 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!

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    v-juanli-msft

     

    Thank you for your explanation. Those calculated columns worked perfectly! I really appreciate your help on this.