The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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?
@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
User | Count |
---|---|
12 | |
8 | |
6 | |
6 | |
6 |
User | Count |
---|---|
24 | |
14 | |
13 | |
9 | |
7 |