cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## Average percentage by month and year

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.

6 REPLIES 6
Super User

@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

New Member

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.

Super User

@Daisyhill , Use that like

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

New Member

@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?

Super User

@Daisyhill , Sorry My mistake, Try like

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

New Member

@amitchandak Thanks and it works!

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors