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
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.

 

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
OwenAuger
Super User
Super User

@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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

3 REPLIES 3
OwenAuger
Super User
Super User

@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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

It works! 

 

Thanks you!

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

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.