Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Calculating an Occupancy Percentage

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

Need Help

10 Replies

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

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

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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/EVlKHibWTHVBkTaG9kYSts0B5mJPkiq6GJrFetpsG55z4g?e=nXs0cZ

       

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        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

  • v-eachen-msft's avatar
    v-eachen-msft
    Community Support
    6 years ago

    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.