cancel
Showing results for
Search instead for
Did you mean:
Regular Visitor

## Summing First values in categories

So this is a little difficult to exchange.

I'm gathering data from teaching information. This includes teaching hours, preparation hours, and assessment hours. Teaching and preparation hours are logged perfectly fine, with unique values for each. However, because of the way the individual units are logged, assessment hours isa essentially duplicated for all entries in that unit. For example:

 Unit 1 Students: 20 teaching hours prep hours assessment hours Geoff 1 2 20 Gary 3 4 20 Jenny 5 6 20 Carol 7 8 20

The assessment hours is based off the number of students, which is a static number for the whole unit. You can see that even with differeing teaching and prep hours, from a reporting point of view, they also each of 20 assessment hours. In this instance, I could just take the MAX value from that column and be done with it. But the problem compounds because each unit can have multiple delivery styles:

 Delivery Style teaching hours prep hours assessment hours Geoff Unit 1 Lecture 2 5 20 Geoff Unit 1 Seminar 4 5 20 Geoff Unit 1 Workshop 1 5 20 Geoff Unit 1 Lecture 4 5 20 Geoff Unit 2 Seminar 5 6 35 Geoff Unit 2 Workshop 3 3 35 Geoff Unit 2 Workshop 2 5 35 Geoff Unit 3 Lecture 6 2 40 Geoff Unit 3 Seminar 4 1 40 Geoff Unit 3 Lecture 8 4 40

You can see that when you add enough units in, and then report by individual, I then have the be able to take the MAX from each Unit rather than the whole column. Sadly, this isn't something I can solve in the way the data is gathered.

What I need is to be able to take the max value for each Unit. Using the example above, I need the assessment total for Geoff to be 95, rather than 305. Hopefully that makes sense. I've tried Summarize, but I keep getting multiple column errors. I think that might be to do with it generating a table, but if it does, I don't know how I could then use the resulting table to add those numbers onto, for example, Geoffs teaching and preparation hours...

HALP!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Summing First values in categories

@EpicTriffid try following measure, change table and column name as per your model.

```Total Assessment Hours =
SUMX(
SUMMARIZE( 'Table', 'Table'[Teacher], 'Table'[Unit] ),
CALCULATE( MAX( 'Table'[assessment hours] ) )
)```

Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

2 REPLIES 2
Super User

## Re: Summing First values in categories

@EpicTriffid try following measure, change table and column name as per your model.

```Total Assessment Hours =
SUMX(
SUMMARIZE( 'Table', 'Table'[Teacher], 'Table'[Unit] ),
CALCULATE( MAX( 'Table'[assessment hours] ) )
)```

Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Regular Visitor

## Re: Summing First values in categories

Yup! Could kiss you!

## Helpful resources

Announcements

#### New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

#### Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

#### Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)