Solved: Sum up the maximum per group, in a measure!

Niki · ‎12-02-2019

Hi, I really need help.

I want to calculate the maximum per group, the slicers in the report will effect the terms of the maximum, zo I need a measure and not a calculated column or table, as they are not effected by the slicers. After I have deterrmined the max, I want to sum it up and show in a visual.

Data is about number of meeting rooms in use at a certain moment (year, week, day, time) by a certain team.

I have a visual showing the times on the x-axis and the maximum number of rooms in use as a value. In the slicer, users can select years, weeks, and days of the week. I have the measure: MaxRooms = Max(numberofrooms), which works fine, because it is working with the slicers and everything, just as long as I have only 1 team selected. As soon as I select a second team, I want to sum up the maximum of the first and the second team. (not get the maximum out of the 2 teams) No clue how to do that!!

Team	date	day of week	name day	Year	WeekNr	Starttijd	Room ID	number of rooms
1	3/4/2019	1	ma	2019	10	8:00	1	1
1	3/7/2019	4	do	2019	10	8:00	1	1
1	3/19/2019	2	di	2019	12	8:00	1	1
2	3/4/2019	1	ma	2019	10	8:00	1	1
2	3/5/2019	2	di	2019	10	8:00	1	2
2	3/8/2019	5	vr	2019	10	8:00	2	1
1	3/7/2019	4	do	2019	10	8:15	1	1
1	3/19/2019	2	di	2019	12	8:15	1	1
2	3/4/2019	1	ma	2019	10	8:15	1	1

Kind regards,

Niki

parry2k · ‎12-02-2019

@Niki try this measure

Max Rooms = SUMX ( VALUES ( Room[Team] ), CALCULATE ( MAX ( Room[number of rooms] ) ) )

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.

View solution in original post

parry2k · ‎12-02-2019

@Niki try this measure

Max Rooms = SUMX ( VALUES ( Room[Team] ), CALCULATE ( MAX ( Room[number of rooms] ) ) )

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.

Niki · ‎12-09-2019

Hi @parry2k ,

Measure is working great, as it should be!

I have a next level question on this measure, maybe you can help me out, (again :))

I now calculate the max value (nr of rooms) per group. I added time of day and day of the week on my X-axis.

Day of the week I only added to my axis to solve my 'problem'.

If I want to show the measure in a KPI box to give the user the max nr of rooms needed in a glance, it is not taking the day of the week into account. (or If I only want to show times of day on my axis and not day of the week)

So if I have 2 groups selected, it calculates the max of group 1 and sums it up to the max of group 2.

I would need the max on the same day and time.

To show on my graph, it would be sufficient to take into account the day of week. To show it in a kpi box I think I also need the time of day int the measure. I have been trying stuff with summarizecolumns, but didn't work.

Any ideas?

Kind regars,

Niki