dividing by unique values. Do not use sum or count

[Mamtas]

I have these 2 tables YTD and Budgets. I need to create a measure in YTD, where it sum the Booked session as per centre , divide that by number of days and whatever the result is, Divide that by staffed places from Budgets table. This way i will get the actaul occupancy rate per centre

Example Centre Alfi occupancy % = ((25+25+25+25)/4) / 46 = 54.3%

I would also like to show the Budget % in table visual to show as a comparison.  That number should always be static

Example Alfi's actual % as above calculated is 54.3%, but their Budget % from Budget table is 87.7%

YTD

Centre	Booked session	Days
ABC	40	Monday
ABC	38	Tuesday
ABC	36	Wednesday
ABC	34	Th ursday
ABC	32	Friday
DEF	15	Monday
DEF	16	Tuesday
DEF	17	Wednesday
DEF	18	Thursday
DEF	19	Friday
alfi	25	Monday
alfi	25	Tuesday
alfi	25	Wednesday
alfi	25	Thursday
ABC	38	Friday
ABC	36	Monday
ABC	34	Tuesday
ABC	32	Wednesday
ABC	30	Thursday

Budgets

Centre names with '	Budget %	Staffed Places
alfi	87.7%	46
DEF	90.2%	71
ABC	87.9%	90

[Mamtas]

Thank Ryan, but no.

First it needs to divide the sum of budget places to the count of number of days and whatever the result is, divide that by staffed places for that particular centre.  

I have tried 2 ways

FIRST WAY

 Measure 2 = 

(sum('YTD'[Booked Sessions]) / count('YTD'[Days_1]))

Then created another mesaure

Measure 3= Measure 2/ sum (Budgets[Staffed Places]'

This works on some centres, but giving me wrong results on other.  

SECOND WAY

I have tried this

Measure 2 =

(sum('YTD'[Booked Sessions])/count('YTD'[Days_1]))

/

LOOKUPVALUE(Budgets[Staffed Places],Budgets[Centre names with '],'YTD'[Centre])

But here lookupvalue function is giving me error   at this place  "'YTD'[Centre]"

Error is    " single value for column 'Centre' in table 'YTD' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.'

I then tried using sum, min , max function , however that does not make any sense here as Centre is a text field. But nothing.

Here is my relationship

[ryan_mayu]

what's the expected output based on the sample data you provided?

[Mamtas]

Calculating the occupancy %

Example Centre Alfi occupancy % = ((25+25+25+25)/4) / 46 = 54.3%

Alfi was open 4 days with 25 occupied places per day, therefore there per day occupancy was 25

((25+25+25+25))/4 = 25

But there budget staffed places were 46 (from budget table)

Therefore their occupancy is 25/46= 54.3

[ryan_mayu]

that's what I get for alfi as well. I used the same logic as you mentioned

[Mamtas]

Which one, 

First way or second ?

[ryan_mayu]

@Mamtas

pls try this

Measure = average('YTD'[Booked session])/sum('budget'[Staffed Places])

[Mamtas]

Thanks mate, it works just like mine. i.e. works for some centres, but others giving wrong calculation.  That's the reason i tried lookup function, but that didnt work either.

sometime Excel seems like a much better option.  At least you can see the calculation steps and see why and where is the mistake.

[ryan_mayu]

then pls update the sample data or provide the output for other center.

So far, I can't figure out the mistake. We need more info.

[ryan_mayu]

@Mamtas 

is this what you want?

Measure = average('YTD'[Booked session])/sum('budget'[Staffed Places])

pls see the attachment below

[Mamtas · Accepted solution]

Hi Ryan,  First of all , thanks so much for your response.  Even after everything, i was not able to get the formulas to work for every centre.  

Finally , i found the solution, by just changing the cross filter direction (as below) to 'Both' in relations.

Thanks again for your help

[ryan_mayu]

you are welcome