cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
efanta Frequent Visitor
Frequent Visitor

Sum the Max of a grouped by

Hi everyone,

 

I´m trying to get a formula that allow me to get the sum of the maximum values in a DB considering that the count of the item is greater than or equal than 3.

 

I create the following example. As the element A and C are repeated 3 or more times in the table, it must added the maximum sales of each of them. For the case of A is 443 and for the case of C is 255, so the total sum should be 698.

 

DateCountrySales
jan-17A45
jan-17B23
jan-17C52
feb-17A443
feb-17C255
mar-17A109

 

Criteria>=3
A443
C255
Total698

 

Please help! Thanks in advance.

 

BR,

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Sum the Max of a grouped by

@efanta

 

There are a couple of ways you could write this measure. The logic is to filter the countries down to those appearing 3+ times, then sum over those.

Here is one method (in this example Countries are filtered out by returning blank if fewer than 3 rows appear):

 

Sum of Max for Countries that appear 3+ times =
SUMX (
    VALUES ( YourTable[Country] ),
    IF (
        CALCULATE ( COUNTROWS ( YourTable ) ) >= 3,
        CALCULATE ( MAX ( YourTable[Sales] ) )
    )
)

Regards,

Owen



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




3 REPLIES 3
Super User
Super User

Re: Sum the Max of a grouped by

@efanta

 

There are a couple of ways you could write this measure. The logic is to filter the countries down to those appearing 3+ times, then sum over those.

Here is one method (in this example Countries are filtered out by returning blank if fewer than 3 rows appear):

 

Sum of Max for Countries that appear 3+ times =
SUMX (
    VALUES ( YourTable[Country] ),
    IF (
        CALCULATE ( COUNTROWS ( YourTable ) ) >= 3,
        CALCULATE ( MAX ( YourTable[Sales] ) )
    )
)

Regards,

Owen



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




efanta Frequent Visitor
Frequent Visitor

Re: Sum the Max of a grouped by

It works! 

 

Thanks you!

Community Support Team
Community Support Team

Re: Sum the Max of a grouped by

@efanta,

 

Here is another way for your reference.

total sum =
SUMX (
    FILTER (
        GROUPBY (
            Table1,
            Table1[Country],
            "Max", MAXX ( CURRENTGROUP (), Table1[Sales] ),
            "Count", COUNTX ( CURRENTGROUP (), 1 )
        ),
        [Count] >= 3
    ),
    [Max]
)
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.