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

v-juanli-msft

 

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