Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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/4 | 6,500 | 6,500 |
Person A | 2018/3 | 4,000 | 4,000 |
Person B | 2018/1 | 23,000 | 23,000 |
Person B | 2018/8 | 6,000 | 6,000 |
Person B | 2018/1 | 6,600 | 0 |
Person B | 2018/2 | 3,300 | 3,300 |
Person B | 2018/5 | 11,000 | 11,000 |
Person C | 2018/4 | 8,600 | 0 |
Person C | 2018/10 | 4,500 | 0 |
Person C | 2018/10 | 6,200 | 6,200 |
Person C | 2018/6 | 5,500 | 5,500 |
Person C | 2018/4 | 9,000 | 9,000 |
Person C | 2018/2 | 15,000 | 15,000 |
Solved! Go to Solution.
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
Thank you for your explanation. Those calculated columns worked perfectly! I really appreciate your help on this.
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.
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 | 0 |
Person A | 2018/3 | 4,000 | 4,000 |
However, when I use the formula you provided me I am getting this as my 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 | 7,000 |
Person A | 2018/3 | 4,000 | 4,000 |
Any other insight you may have??
It worked perfectly for me. I cannot say what mistake you are committing. Retry please.
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 @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
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.
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.
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |