Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Maximum of Sum of Maximums within a month

Hi all

 

I'm struggling to get a visualisation working as I would like it to.

 

My scenario is as follows:

 

I'm tracking room occupancy every hour for a number of rooms within a building

 

e.g

DateRoomOccupancy
2022-07-30 12:00:0011
2022-07-30 12:00:0026
2022-07-30 13:00:0018
2022-07-30 13:00:0025
2022-07-31 12:00:0013
2022-07-31 12:00:0027
2022-07-31 13:00:0012
2022-07-31 13:00:0024
2022-08-01 10:00:0010
2022-08-01 10:00:0023
2022-08-01 11:00:0014
2022-08-01 11:00:0022


What I am looking to do is to find what the maximum occupancy at any point in time is for

 

* The Hour of the Day

* The Day

* The Month

* The Year

 

The aim is to produce a drill down graph, starting at a year, that will show the maximum number of people in the building at any one time in the period

 

So in the example above the figures would be

 

Time Period Maximum Total Occupancy
2022-07-30 12:00:007
2022-07-30 13:00:0013
2022-07-3013
2022-07-31 12:00:0010
2022-07-31 12:00:006
2022-07-3110
July13
2022-08-01 10:00:003
2022-08-01 11:00:006
2022-08-016
August6
202213


So far I have come up with the following DAX formula

 

Peak Occupancy = SUMX(
KEEPFILTERS(VALUES('Data'[room])),
CALCULATE(MAX('Data'[Occupancy]))
)

 


Which is giving me the maximum total per room, which is then summed up, but this does not work when you roll up to the month as I end up with the sum of the maximum of each room on any day of the month - e.g. in my scenario, for July it would take the 7 from Room 2 at 2022-07-31 12:00:00 and the 8 from Room 1 at 2022-07-30 13:00:00 and give a total of 15 -which is not what I want as there has never been 15 people in the building at any one time.

 

Some examples of the drill graph I want to construct - which illustrates the problem I have.  Here June has a peak of 60

 

neilp_2-1659434148871.png

 

 

Drill down to the day breakdown - which shows no one day had more than 39

 

neilp_3-1659434187875.png

 

 

Is this even possible?

1 REPLY 1
Greg_Deckler
Community Champion
Community Champion

@Anonymous Should be possible, seems like a combination of Measure Aggregation and MM3TR&R. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.

 

And this: Matrix Measure Total Triple Threat Rock & Roll - Microsoft Power BI Community



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.