Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

 

1 ACCEPTED SOLUTION

Hi @Anonymous

Create a index column from the Query Editor

1.png

 

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]
)
)

2.png

 

Best Reagrds

Maggie

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

@v-juanli-msft

 

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

Anonymous
Not applicable

@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??

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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!

Hi @Anonymous

Create a index column from the Query Editor

1.png

 

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]
)
)

2.png

 

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.