Average percentage by month and year

[Daisyhill]

Hi,

I have been looking and trying differnt solution on this forum, but still get my head around to do this.  I hope someone will be able to help.

This is the data I'm working on.  I want to calculte the average occupany rate per month for each building.

I managed to create 2 calcualted columns for Month and Year Number. 

I also I created a measure to calculate the daily occupancy rate --> Occupancy Rate = DIVIDE([Daily Count],AVERAGE('Tbl_Security_Data'[Building Capacity])

But now I'm stuck at the last step to get the monthly average, for example, in July should be 20% .

I never have any sort of training on DAX, I only know some of the functions can't be used in combination of meausres / calculated columns?!?  This is why I'm so confused I can't use all the existing data to create what I want.

Thanks in advance for any help.

[amitchandak]

@Daisyhill , Create a measure like

Occupancy Rate = DIVIDE(Sum('Tbl_Security_Data'[Daily Count]),Sumx(Summarize('Tbl_Security_Data', 'Tbl_Security_Data'[Date],[Building Capacity])))

Assuming Daily Count is a column

[Daisyhill]

Thanks @amitchandak 

I can't create a measure with daily count, this column is a measure - 

Daily Count = CALCULATE(DISTINCTCOUNT('Tbl_Security_Data'[Token number])).  Am I making too complicated for myself?

I can rearrange the data showing the token number instead, it will show the exact number (i.e. 22 counts on 8 July) like this, is it possible to create a measure with these instead?

Thanks.

[amitchandak]

@Daisyhill , Use that like

Occupancy Rate = DIVIDE([Daily Count],Sumx(Summarize('Tbl_Security_Data', 'Tbl_Security_Data'[Date],[Building Capacity])))

[Daisyhill]

@amitchandak  Thanks, really appreciate for your help.

I tried but it comes up with an error:

Is it to do with the format of the columns?

Also, is the measure you created to return the daily occupancy rate or monthly average?

[amitchandak]

@Daisyhill , Sorry My mistake, Try like

Occupancy Rate = DIVIDE([Daily Count],Sumx(Summarize('Tbl_Security_Data', 'Tbl_Security_Data'[Date],[Building Capacity]), [Building Capacity]))

[Daisyhill]

@amitchandak Thanks and it works!

But this only showing the daily occupancy rate, is it possible to calculate a monthly average like this: