How to sum by day and then average ?

[Anonymous]

Hello

I have a fact table with the following components. First 3 columns are connected to their specific dimension tables on a many to one relationship. Column 4 shows a percentage value which is an occupation value of that room. If I sum all the occupation values for each room on each day, I will get the total occupation for that day. In my example below, room 1 on 20220901 has 0.75, 0.6 on 20220902 and 0.3 on 20220903.

idDimTime	idDimRoom	idDimSession	DailyOccupation
20220901	1	1	0.25
20220901	1	2	0.25
20220901	1	3	0.25
20220902	1	5	0.5
20220902	1	6	0.1
20220903	1	7	0.1
20220903	1	8	0.1
20220903	1	9	0.1

I require through DAX to sum all the occupation values by day and then average them to get a montly occupation value.

will appreciate your advise

[amitchandak · Accepted solution]

@Anonymous , A new measure

Averagex(Values(Table[idDimTime]), calculate(Sum(Table[DailyOccupation] )) )