Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
@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] ) ) )
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@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] ) ) )
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Yup! Could kiss you!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
76 | |
63 | |
55 |
User | Count |
---|---|
142 | |
107 | |
89 | |
84 | |
65 |