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.
Hello All,
I am working on a project in which:
I have occupancy data for rooms where the occupancy data is collected every 5mins
I have meeting data which has a meeting start and end time.
What I would like to do is come up with a formula that allows me to do the following:
Where the room & Date are the same between the tables, Average all of the Average Occupancies between Meeting Start & End Time.
I want this to be dynamic and capable of working on the rest of the data set. The tables above are simply filtered to only 1 room on a given day between a time frame. I want this to be able to calculate the average occupancy for any room on any date within any time frame for each meeting.
Thanks in advance and truely appreciate all the help I can get.
Solved! Go to Solution.
Hi @Anonymous
Do you use this formula to create a calculated column in the table1->Meetings?
If so, please modify with the following formula
average = CALCULATE( AVERAGE(Censor[Average Occupancy (%)]), FILTER( ALLEXCEPT(Censor, Censor[Space Name]), [Date] = Meetings[Start Date] && [Date Time] >= EARLIER(Meetings[Start Time]) && [Date Time] <= EARLIER(Meetings[end time]) ) )
If it doesn't solve your problem,
could you create a pbix only contains some rows of these two table and apply your formula as the screenshot and then share with me?
Best Reagrds
Maggie
@Anonymous
Are you able to post your file or a sample of your data?
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
I cannot share the data how it currently stands as for it does have private data inside.
However with the 2 pictures this should give enough of what I want the formula to be capable of doing
Essentially the total in the "Occupancy" table needs to be able to relate to the row of data in the "Meeting" table so that I could place a measure in the "Meeting" table that averages all the occupancy rows for a meeting that has specific start date, time and end time.
Ok, could you walk me through a numeric example ? Thx
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Not sure how to better explain this, but here is an attempt.
What needs to be done is I need to write up a measure which will be placed in this table
Where we can see from the other table it is the same date, same room and all the occupnacy rows are between the start time and end time...
so for the "new measure column" it would take the total average of all the rows below
So in the first table we would have 29.60 display in the new column.
Basically I think the formula needs to follow this type of pragmatic thinking:
When Room # & Date is the same between both tables
Average all of the Average occupancies
Where the Date time is between the start time and end time of the meeting table
Hopefully this helps?
I've been trying to put something together, but am quite new to DAX.
essentially I think something similar to this would work
If('Meetings'[Room] == 'Censor'[Room] && 'Meetings'[Start Date] == 'Censor[Date] && 'Censor'[Date Time] >= 'Meetings'[Start Time] && 'Censor'[Date Time] <= 'Meetings'[EndTime], Average('Censor'[Average Occupancy %]) )
This isn't the correct syntax, but think it could help
Hi @Anonymous
Create a calculated column as below (sheet5->table1, sheet6->table2)
average = CALCULATE(AVERAGE(Sheet6[av]),FILTER(ALLEXCEPT(Sheet6,Sheet6[room]),
[date]=Sheet5[start date]&&[datetime]>=EARLIER(Sheet5[start time])
&&[datetime]<=EARLIER(Sheet5[end time])))
Best Regards
Maggie
thank you for your help however I do seem to have a problem still in regards to this measure.
average = CALCULATE( AVERAGE(Censor[Average Occupancy (%)]), FILTER( ALLEXCEPT(Censor, Censor[Space Name]), [Date] = Meetings[Start Date] && [Date Time] >= EARLIER([Start Time]) && [Date Time] <= EARLIER(Meetings[end time]) ) )
this happens to leave the average column all blank (below is pictures of the tables whited out with anything I can't share
Meetings table (table 1)
table 2
I am honestly not certain as to why this would display all blank
Thank you for your help thus far, highly aprreciated
Hi @Anonymous
Do you use this formula to create a calculated column in the table1->Meetings?
If so, please modify with the following formula
average = CALCULATE( AVERAGE(Censor[Average Occupancy (%)]), FILTER( ALLEXCEPT(Censor, Censor[Space Name]), [Date] = Meetings[Start Date] && [Date Time] >= EARLIER(Meetings[Start Time]) && [Date Time] <= EARLIER(Meetings[end time]) ) )
If it doesn't solve your problem,
could you create a pbix only contains some rows of these two table and apply your formula as the screenshot and then share with me?
Best Reagrds
Maggie
The formula was correct it was the modeling of the data that was incorrect. The time feilds came in as date time and I guess this didn't work properly due to that. thanks
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |