Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
ConfusedTime
Frequent Visitor

Sum by subgroup after returning a MAX value

Hi Everyone,

 

I'm having a problem working out how to do the following, I'm not sure if it's something I'll need to do at the data level or if a measure can do it.

 

I have data setup like the following:

 

OrganisationServiceNameAvailabilityCapacity

Service

 

Club AClub Athletics024

Archery

 

Club AClub Athletics024Swimming
Club AClub Athletics024Tennis
Club AClub Athletics024Basketball
Tennis ClubWimbledon136Tennis
Tennis ClubWimbledon136Squash
Diving SchoolSwimming Pool Lane013Swimming
Football CampNou Camp016Football
Driving SchoolParis357F1
Driving SchoolParis357F2
Driving SchoolBerlin741F1
Driving SchoolBerlin741F2
Driving SchoolMilan635F1
Driving SchoolChicago252F1

 

The result I am trying to return in a matrix is grouped by Organisation Name, then service name and then service.  The problem I have got is that there are multiple lines for each provider relating to capacity, so for example, Driving school Berlin has 41 places overall which can be filled with either F1 or F2, not 82 places which is how it would be summed.  I've tried writing a max measure which works at a servicename level, but then at organisation level it just returns the max value of services so for driving school 57 is returned when i want it to be 185.

 

The above would be summed as follows:

 

Club A capacity 24    
Tennis Club capacity 36    
Diving School capacity 13    
Football Camp capacity 16    
Driving School Capacity 185Paris 57Berlin 41Milan 35Chicago 52

 

This is the measure i currently have:

 

Max Capacity =

CALCULATE (

    MAX(‘table'[Capacity]),

    FILTER (

        ALLEXCEPT ( ‘table', ‘table'[Organisation] ),

        ‘table’[Capacity] = MAX ( ‘table’[Capacity])

    )

)

 

Any help or pointers would be great as I'm going round in circles.

 

thanks in advance

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@ConfusedTime Try this:

Measure = 
    VAR __Table = SUMMARIZE('Table', [Organisation], [ServiceName], "__Capacity", AVERAGE('Table'[Capacity]) )
    VAR __Result = SUMX(__Table, [__Capacity])
RETURN
    __Result

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
ConfusedTime
Frequent Visitor

Thank you so much!!! This works perfectly! 🙂

Greg_Deckler
Super User
Super User

@ConfusedTime Try this:

Measure = 
    VAR __Table = SUMMARIZE('Table', [Organisation], [ServiceName], "__Capacity", AVERAGE('Table'[Capacity]) )
    VAR __Result = SUMX(__Table, [__Capacity])
RETURN
    __Result

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors