Need help calculating totals and weekly percentages for groups.

[Anonymous]

Hi all,

I need help with some measures please. I'm creating a report that shows the number of available appointments and room capacity utilisation %, based on data from booking system.

I have created a table visual in my report as shown below. Please note in the table there is a row for each room per day of the current week (for example 11-17 Jan). 

Room (from data table)	Date (from data table)	Daily appointments available (measure)	Daily Capacity Utilised (%) (measure)
London ROOM 1	11 Jan	30	3.23
London ROOM 2	11 Jan	25	19.35
Brighton ROOM 1	11 Jan	21	16
Brighton ROOM 2	11 Jan	22	12
Birmingham Room 1	11 Jan	5	5%
Birmingham Room 2	11 Jan	10	10%
Birmingham Room 3	11 Jan	15	15%
London ROOM 1	12 Jan	30	3.23

I want to create a second visual table, as shown below, that calculates the total available appointments for the week, in addition to the total weekly capacity utilised %, per Room Location (so I grouped the rooms)

Room Location	Total Appointments available this week	Weekly Capacity Utilised (%)
London
Brighton
Birmingham

I need help with the measures for this please. 

Please note the London room is open 7 days a week, Brighton 6 days, and Birmingham 5 days.

Thanks.

[v-easonf-msft]

Hi, @Anonymous 
Please show your relevant measures' formulas.

If possible , can you upload a sample pbix to OneDrive and  share the link here  for test?

You can take a try formula as below:

Measure2_Daily appointments available = sumx('Table',[Measure_Daily appointments available])

Best Regards,
Community Support Team _ Eason

[Anonymous]

Hi @amitchandak 

Thanks for your reply.

I should have mentioned that the underlying data only contains appointments for the current week, so theres no need to distinguish which week the appointment is for.  Essentially what I need is for the numbers in the below column to be totaled up (by room location, for example London).

I've tried to create a measure like the one you suggested to do this, but its my understanding I can't use sum for a measure. How do I sum measures?

Additionally, I need help with the second column, which works out the total capacity utilised. If you could give me any pointers on this id really appreciate it.

Thanks!

[amitchandak]

@Anonymous , if available this week is ums from daily. You can have week in you date table and create the measures 

example with help from date table

new columns in date table

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)

or
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYMM format

measures example

This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))