Forum Discussion

jonbox's avatar
jonbox
Helper II
4 years ago
Solved

Count For each person

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 ownerProjectResource Used
BenProj130%
BenProj220%
BenProj370%
AmandaProj130%
AmandaProj250%
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
    parry2k
    Super User
    4 years ago

    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's avatar
      jonbox
      Helper II
      4 years ago

      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 ownerProjectResource Used
      BenProj130%
      BenProj220%
      BenProj370%
      AmandaProj130%
      AmandaProj250%

       

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

       

      Project ManagerOverall Resource Used
      Ben120%
      Amanda80%

       

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

       

       

  • parry2k's avatar
    parry2k
    Super User
    4 years ago

    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  )
)