Forum Discussion
Need help with summing max function
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
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 | |||
| client | YearMonth | words | MaxWords |
| Person A | 2018/1 | 7,000 | 7,000 |
| Person A | 2018/5 | 12,000 | 12,000 |
| Person A | 2018/1 | 6,500 | - |
| Person A | 2018/3 | 6,600 | 6,600 |
| Person A | 2018/1 | 7,000 | 7,000 |
| Person A | 2018/8 | 6,000 | 6,000 |
| Person A | 2018/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!
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.