cancel
Showing results for
Did you mean:

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

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:

 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.

1 ACCEPTED SOLUTION
Super User

@ConfusedTime Try this:

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
2 REPLIES 2
Frequent Visitor

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

Super User

@ConfusedTime Try this:

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Fabric Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors