Chart for Library Student Occupency Chart

[MayAnk__Rathi]

I have study library of 70 seating capacity. All data is only in this sheet.

I want to create a chart in which I get libraray seat occupancy status by colour. Whenever seat is occupied then Green colour else Red colour. If a seat with Full shift then same is displayed by large circle and if with Morning or Evening shift same is displayed by small circle, and inside the circle seat number.

Status is automatically update when column J's date gone.

Attached a specimen of chart which I want in below format with my data & data headers.

[v-veshwara-msft]

Hi @MayAnk__Rathi ,

May I ask if the responses provided has addressed your needs? If so, please consider marking it as Accepted Solution to help others with similar queries.

If you need any further assistance, feel free to reach out.

Thank you.

[v-veshwara-msft]

Hi @MayAnk__Rathi ,

Just checking in to see if you query is resolved and if any responses were helpful. If so, kindly consider marking the helpful reply as 'Accepted Solution' to help others with similar queries. 

Otherwise, feel free to reach out for further assistance.

Thank you.

[MayAnk__Rathi]

Not resolved, even unable to understand your provided solution 

[v-veshwara-msft]

Thanks for getting back, and sorry for any confusion earlier.

To clarify, the standard Scatter Chart in Power BI can be used to some extent, but it has limitations - especially when it comes to showing seat numbers inside the bubbles and customizing the bubble size and color based on different conditions.

You can:

Use X and Y fields to position each seat

Use Shift_Size for bubble size

Use Color or Status for the legend

But the default scatter chart doesn’t support showing seat numbers inside the bubbles directly.

For better results, please try these Custom Visuals:
  1. Scatter/Bubble Chart by xViz.
  2. Bubble chart by xViz.
  3. Bubble Chart with Categorical Data.

If you're exploring this in Excel, for better guidance, we suggest posting in the Excel Community.

Hope this helps. Please reach out for further assistance.
If this post helps, then please consider to give a kudos and Accept as the solution to help the other members find it more quickly.

Thank you.

[v-veshwara-msft]

Hi @MayAnk__Rathi ,

Just following up to check if the above response was helpful.

Also, have you had a chance to post in the Excel Community for the Excel-specific approach?

If you find any response helpful, please consider marking it as the 'Accepted Solution' to assist others with similar queries.

Feel free to reach out if you need further assistance.

Thank you.

[v-veshwara-msft]

Hi @MayAnk__Rathi ,

Thanks for reaching out to Microsoft Fabric Community and for sharing the detailed requirements for the seating occupancy chart.

To ensure we provide the most accurate guidance, could you please confirm whether you are building this chart in Excel or Power BI?

Some features, such as conditional formatting, bubble chart customization, and dynamic label placement, can vary depending on the platform. Once we have this information, we can guide you with the right solution and steps accordingly.

Looking forward to your clarification.

[MayAnk__Rathi]

I want to prepare this chart in excel and also in Power BI

[v-veshwara-msft]

Thank you for clarifying that you'd like to prepare this chart in both Excel and Power BI. Here's how you can proceed:

For Excel:
The bubble chart requirements you’ve described such as variable circle sizes based on shifts, colored circles for occupancy status, and seat numbers inside the bubbles are best suited for Excel’s Bubble Chart feature.
To get more tailored guidance for Excel, I’d recommend posting your question in the Excel Community: Welcome to the Excel Community | Microsoft Community Hub
This way, Excel experts can provide you with detailed steps and templates for your scenario.

Here is a similar discussion in Excel Community: Excel bubble chart: how to size bubbles based on no. of items with identical x and y values? | Micro...

For Power BI:
While Power BI does not natively support Bubble Charts in the same way Excel does, you can achieve a similar visual by using a Scatter Chart visual. Here's how you could approach it:

• Use X and Y coordinates to arrange seat positions in a grid.

• Use a measure or column for the Size field to represent shift types (larger size for full shift, smaller for part shifts).

• Use Conditional Formatting on the color field to indicate occupancy status (e.g., green for occupied, red for vacant).

• For seat numbers, use the Data Labels option in the scatter chart. Note that label placement might be limited compared to Excel.

Hope this helps. Please reach out for further assistance.
If this post helps, then please consider to give a kudos and Accept as the solution to help the other members find it more quickly.

Thank you.

[BhavinVyas3003]

Hi @MayAnk__Rathi ,

1. Create a helper table with all 70 seat numbers and a formula that checks if the seat is occupied and shift type, based on today's date compared with Column J (=IF(J2<TODAY(), "Vacant", ...)).

2. Add columns to determine:

• Occupancy status (Occupied/Vacant)
• Shift type (Full/Morning/Evening)
• Circle size (use a helper field with values like 2 for full shift and 1 for part shifts)
• Color (use conditional logic like =IF(Status="Occupied", "Green", "Red"))

3. Insert a Bubble Chart:

• Use X and Y coordinates to position seats (you can arrange them in a grid manually)
• Use bubble size for shift (small/large)
• Use color logic with a custom VBA macro or conditional formatting via data labels (native Excel charts don’t directly support different colored bubbles with label text).

4. Enable auto-refresh using TODAY() in formulas — this ensures Column J checks stay updated.

[MayAnk__Rathi]

Point no. 1 is already done, point no. 2 is done.

But point no. 3 and 4 goes above the head. Please help

[GilbertQ]

Hi @MayAnk__Rathi 

I'm unsure how you could get the two small circles to change when you have got multiple value. But for the colors, you could use conditional formatting to say, when it is occupied, make it red else when it is unoccupied. Make it green. Here is an example how you do conditional background color format? Apply Conditional Table Formatting in Power BI - Power BI | Microsoft Learn

[MayAnk__Rathi]

seating capacity is fixed upto 70, each circle having inside seat number