Sum by subgroup after returning a MAX value

[ConfusedTime]

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:

Organisation	ServiceName	Availability	Capacity	Service
Club A	Club Athletics	0	24	Archery
Club A	Club Athletics	0	24	Swimming
Club A	Club Athletics	0	24	Tennis
Club A	Club Athletics	0	24	Basketball
Tennis Club	Wimbledon	1	36	Tennis
Tennis Club	Wimbledon	1	36	Squash
Diving School	Swimming Pool Lane	0	13	Swimming
Football Camp	Nou Camp	0	16	Football
Driving School	Paris	3	57	F1
Driving School	Paris	3	57	F2
Driving School	Berlin	7	41	F1
Driving School	Berlin	7	41	F2
Driving School	Milan	6	35	F1
Driving School	Chicago	2	52	F1

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 185	Paris 57	Berlin 41	Milan 35	Chicago 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

[ConfusedTime]

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

[Greg_Deckler · Accepted solution]

@ConfusedTime Try this:

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