cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Need help with summing max function

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
Highlighted
Super User IV
Super User IV

Re: Need help with summing max function

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/
Highlighted
Anonymous
Not applicable

Re: Need help with summing max function

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

Highlighted
Super User IV
Super User IV

Re: Need help with summing max function

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/
Highlighted
Anonymous
Not applicable

Re: Need help with summing max function

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!

Highlighted
Super User IV
Super User IV

Re: Need help with summing max function

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/
Highlighted
Community Support
Community Support

Re: Need help with summing max function

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

Highlighted
Anonymous
Not applicable

Re: Need help with summing max function

@v-juanli-msft

 

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

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors