Count For each person

[jonbox]

Hi,

I'm looking to create a measure to count results for each person:

e.g. below it should count ben = 120% resource used, amanda 80% resource used.

I'd also like a count for number of project owners who's resource used is above 100% e.g. since ben is over 100% he should be counted, whereas amanda shouldn't. 

Thanks!

Project owner	Project	Resource Used
Ben	Proj1	30%
Ben	Proj2	20%
Ben	Proj3	70%
Amanda	Proj1	30%
Amanda	Proj2	50%

[v-stephen-msft · Accepted solution]

Hi @jonbox ,

If you want to calculate the resource used rate by project owner, you can create the following measure.

total resource used = CALCULATE(SUM('Table'[Resource Used]),ALLEXCEPT('Table','Table'[Project owner]))

If you want to count the project owners who use more than 100% of the resource, you can create the following measure.

count = CALCULATE(DISTINCTCOUNT('Table'[Project owner]),FILTER('Table',[total resource used]>1))

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@jonbox it should work based on your sample data, you just need to add another measure to show in the visual:

Sum = SUM ( Table[Resource Used] )

Count = 
SUMX ( VALUES ( Table[Project Owner] ),
    VAR __sum = [Sum]
    RETURN
    IF ( __sum > 100, 1  )
)

[parry2k]

@jonbox try this measure

Count = 
SUMX ( VALUES ( Table[Project Owner] ),
    VAR __sum = CALCULATE ( SUM ( Table[Resource Used] ) )
    RETURN
    IF ( __sum > 100, 1, 0 )
)

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[jonbox]

Hi Parry2k, Thanks for the reply.

I'm not sure your solution has worked as I intended as this is returned:

As i mentioned, I'd like one measure to calculate the over all capacity used (which i could then show in a new table similar to above). 

Project owner	Project	Resource Used
Ben	Proj1	30%
Ben	Proj2	20%
Ben	Proj3	70%
Amanda	Proj1	30%
Amanda	Proj2	50%

Using the example of ben and amanda above, i'd like to calculate their totals and show in a table similar to below:

Project Manager	Overall Resource Used
Ben	120%
Amanda	80%

and THEN a new measure to count all who's resource used is over 100% to show on a card.