Calculating an Occupancy Percentage

[Anonymous]

Firstly, my apologies for this being such a simple question, but I am in my first few days of Power BI and doing some COVID related modelling and I've hit a wall.

I am trying to work out occupancy of cinemas in time slots by day.

I have a transactions table that includes admissions and number of screenings in it. To clarify that is real data downloaded from February. The number of screenings is the DISTINCTCOUNT of a unique ID for each screening.

In a separate lookup table I have by cinema screen a capacity number per screening.

I think what I am trying to calculate is:   Total admissions / (number of screenings * capacity from capacity table)

I've got myself tied in knots on whether that needs DAX to calculate the %age etc etc. I think its just late and my head is fried.

Many thanks for any help you can offer.

Dougie

[v-eachen-msft]

Hi @Anonymous ,

Could you please share your sample data and expected result to me if you don't have any Confidential Information? Please upload your files to OneDrive for Business and share the link here.

[amitchandak]

@Anonymous , Assuming Trasansaction table can join with a capacity table using screen number of screen ID

If transactions[number of screenings] is count/sum like 1,2,3 etc

divide(sum(transactions[admissions]),sumx(transactions,transactions[number of screenings]*related(capacity,[capacity])))

If screen Creen Number like S1, or 1, not the total of screen

divide(sum(transactions[admissions]),sumx(transactions,1*related(capacity,[capacity])))

[Anonymous]

Hi @amitchandak , @v-eachen-msft , @parry2k 

I've tied myself in knots with this! I have attached an anonymised, abbreviated sample of data here for one day

https://cmiscotland-my.sharepoint.com/:x:/g/personal/dougie_cameron_cmi-scotland_co_uk/EVlKHibWTHVBk...

The link between the two data tables is the Cinema field. There are variable number of screenings per day per screen and I created a field of "Unique_ID"

So, I have a measure of screenings in my transactions data table which is 

Screenings = DISTINCTCOUNT(Transactions_Data[Unique_ID])

Now I want the number of screenings x capacity per screening to have a filter relevant capacity.

This is my first attempt at Power BI, and I love it but I'm really struggling to get my head around the syntax!

Many thanks 

Dougie

[Anonymous]

Sorry, should have said that finding the denominator for occupancy ie the capacity is causing me more problems that the actual occupancy %.

Thank you friends.

Dougie

[HotChilli]

Can you confirm that you have a relationship set up correctly between the tables?

The data in the attached file requires some manipulation first (You said further up that "The link between the two data tables is the Cinema field" but at the moment the fields won't match).

Also what is your desired output ? (maybe you can show with reference to the attached data)

[Anonymous]

Thanks @HotChilli 

My apologies! I made an error when anonymising the data! Revised data set here: 

https://cmiscotland-my.sharepoint.com/:x:/g/personal/dougie_cameron_cmi-scotland_co_uk/EVlKHibWTHVBk...

The real live data model and the table connection is shown in the picture.

Let me try and explain the end point.

Each cinema has roughly 4 screenings per day, 28 per week etc. In any day I can easily see total admissions by creating a measure on "Qty".

But I want to be able to relate that to the potential capacity of screenings * capacity.

I have a measure that is the number of screenings using distinctcount on unique_ID, but now I am really struggling to create the multiplication between the new screenings measure in the transactions table and capacity in the capacity table, so that when I apply filters I get whatever cut I want for example by date, name of film etc.

I hope that makes a bit more sense, thank you very much for your time,

Dougie

[HotChilli]

The model looks fine. 

Give me an example of the desired output.  Words help but the figures will show me what you want,

[Anonymous]

Thank you for your patience @HotChilli !

The picture is an idea of the kind of output I'd like to get (i've just used random data to illustrate the . The second table is part that I am really struggling to get to ie variable numbers of screening per day per screen multiplied by a fixed capacity.

The Morning, Afternoon etc are a column I already have in my data model

Thank you

Dougie

[Anonymous]

Hi folks

One last try to see if anyone can point me in the right direction.

Thanks

Dougie

[parry2k]

@Anonymous I would recommend to share sample data in excel sheet using one drive/google drive and will get you the solution.