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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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

Follow on LinkedIn
@ 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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.