Maximum of Sum of Maximums within a month

Anonymous — Tue, 02 Aug 2022 09:57:19 GMT

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

Date	Room	Occupancy
2022-07-30 12:00:00	1	1
2022-07-30 12:00:00	2	6
2022-07-30 13:00:00	1	8
2022-07-30 13:00:00	2	5
2022-07-31 12:00:00	1	3
2022-07-31 12:00:00	2	7
2022-07-31 13:00:00	1	2
2022-07-31 13:00:00	2	4
2022-08-01 10:00:00	1	0
2022-08-01 10:00:00	2	3
2022-08-01 11:00:00	1	4
2022-08-01 11:00:00	2	2

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:00	7
2022-07-30 13:00:00	13
2022-07-30	13
2022-07-31 12:00:00	10
2022-07-31 12:00:00	6
2022-07-31	10
July	13
2022-08-01 10:00:00	3
2022-08-01 11:00:00	6
2022-08-01	6
August	6
2022	13

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

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

Is this even possible?

Re: Maximum of Sum of Maximums within a month

Greg_Deckler — Tue, 02 Aug 2022 11:46:30 GMT

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

topic Maximum of Sum of Maximums within a month in DAX Commands and Tips

Maximum of Sum of Maximums within a month

Re: Maximum of Sum of Maximums within a month