cancel
Showing results for
Did you mean:
Highlighted
efanta 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.

 Date Country Sales jan-17 A 45 jan-17 B 23 jan-17 C 52 feb-17 A 443 feb-17 C 255 mar-17 A 109

 Criteria >=3 A 443 C 255 Total 698

BR,

1 ACCEPTED SOLUTION

Accepted Solutions 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

Proud to be a Datanaut!

3 REPLIES 3 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

Proud to be a Datanaut!

efanta Frequent Visitor

## Re: Sum the Max of a grouped by

It works!

Thanks you! Community Support Team

## Re: Sum the Max of a grouped by

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.