cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EpicTriffid Regular Visitor
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 1Students: 20  
 teaching hoursprep hoursassessment hours
Geoff1220
Gary3420
Jenny5620
Carol7820

 

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 Styleteaching hoursprep hoursassessment hours
GeoffUnit 1Lecture2520
GeoffUnit 1Seminar4520
GeoffUnit 1Workshop1520
GeoffUnit 1Lecture4520
GeoffUnit 2Seminar5635
GeoffUnit 2Workshop3335
GeoffUnit 2Workshop2535
GeoffUnit 3Lecture6240
GeoffUnit 3Seminar4140
GeoffUnit 3Lecture8440

 

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





View solution in original post

2 REPLIES 2
Super User
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.





View solution in original post

EpicTriffid Regular Visitor
Regular Visitor

Re: Summing First values in categories

Yup! Could kiss you! 

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

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

MBAS 2020

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

Difinity Conference

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

Top Solution Authors
Top Kudoed Authors (Last 30 Days)