Sum the Max of a grouped by

[efanta]

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

Please help! Thanks in advance.

BR,

[OwenAuger · Accepted solution]

@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

[efanta]

It works! 

Thanks you!

[v-chuncz-msft]

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