Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Fiona83
Frequent Visitor

Measure to sum monthly capacity and usage

Hi There

I urgently need help with this

 

I am trying to figure out how to show how much each person is being utilized every month out of their total capacity in a stacked graph.

 

I have appended query showing

Yellow - how many days each week a person is booked on a project (allocated)

Blue - How many days they are available that week (capacity)

 

Fiona83_0-1669197202153.png

 

 

I think I need to add a measure, but not sure what calculation will work.

 

No 1 is my primary visual showing all allocated, but when I filter this by date my 2nd visual aligns to allocated and removes the capacity. Ideally, i would like this 2nd visual as a stacked graph but haven't been able to make that work 

 

Fiona83_1-1669197243070.png

https://drive.google.com/file/d/1WCf6YP8mE2BlSndGF92hrnZGQquKg6-1/view?usp=share_link

 

any help on this would be brilliant

 

Thank you

2 REPLIES 2
Fiona83
Frequent Visitor

@Greg_Deckler 

 

Thank you for your reply!!

 

Not sure what's going wrong., but it's not working correctly...

Fiona83_0-1669213788210.png

Updated file:

https://drive.google.com/file/d/1WCf6YP8mE2BlSndGF92hrnZGQquKg6-1/view?usp=share_link

 

 

Greg_Deckler
Super User
Super User

@Fiona83 Seems like it should be something along the lines of:

Utilization % = 
  VAR __allocated = SUMX(FILTER('Table',[Source] = "allocation"),[Days])
  VAR __capacity = SUMX(FILTER('Table',[Source] = "Capacity"),[Days])
  VAR __Result = DIVIDE(__allocated, __allocated + __capacity,0)
RETURN
  __Result

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors