Forum Discussion

KimJ's avatar
KimJ
New Member
4 years ago
Solved

PowerBi Count with Multiple criteria

I have a dataset that I am trying to calculate ratios for. I am successful with a some but am getting hung up on 1 in particular, Employee/(GF+Supervisor) So the Count of employees divided by the combined count of General Foreman and Supervisors.
My dataset has a column for Job Type (which is either Employee, General Foreman, Supervisor, or Other.
It seem so simple yet I’m struggling with it.

Employee/GF+Supervisor = DIVIDE(
CALCULATE(COUNT('Data for PowerBi'[Job Type]),'Data for PowerBi'[Job Type] = "Employee"),
CALCULATE(COUNT('Data for PowerBi'[Job Type]),'Data for PowerBi'[Job Type] = "Supervisor"),('Data for PowerBi'[Job Type]),'Data for PowerBi'[Job Type] = "General Foreman"))

 

As an example, this function is successfully working:
Employee/GF = DIVIDE(
CALCULATE(COUNT('Data for PowerBi'[Job Type]),'Data for PowerBi'[Job Type] = "Employee"),
CALCULATE(COUNT('Data for PowerBi'[Job Type]),'Data for PowerBi'[Job Type] = "General Foreman"))

THANK YOU

Need Help
  • TheoC's avatar
    TheoC
    4 years ago

    Hi KimJ 

     

    I recommend breaking the measure up into multiple measures (i.e. Count Employees, Count General Foreman, etc) just so that you have better control of this. But hopefully the below works for you. I haven't tested syntax.

     

    Measure = 

    VAR _1 = CALCULATE ( COUNTROWS ( 'Table' ) , FILTER ( ALL ( 'Data for PowerBI'[JobType] ) = "Employee" ) )
    VAR _2 = CALCULATE ( COUNTROWS ( 'Table' ) , FILTER ( ALL ( 'Data for PowerBI'[JobType] ) = "General Foreman"  && 'Data for PowerBI'[JobType] ) = "Supervisor" ) )

    RETURN

    DIVIDE ( _1 , _2 , 0 )

     All the best - Theo 

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi KimJ ,

     

    You may try TheoC 's solution. I think it will work.

    If not, is it possibile to provide some dummy data and expected results?

     

    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.

  • TheoC's avatar
    TheoC
    Community Champion
    4 years ago

    Hi KimJ 

     

    I recommend breaking the measure up into multiple measures (i.e. Count Employees, Count General Foreman, etc) just so that you have better control of this. But hopefully the below works for you. I haven't tested syntax.

     

    Measure = 

    VAR _1 = CALCULATE ( COUNTROWS ( 'Table' ) , FILTER ( ALL ( 'Data for PowerBI'[JobType] ) = "Employee" ) )
    VAR _2 = CALCULATE ( COUNTROWS ( 'Table' ) , FILTER ( ALL ( 'Data for PowerBI'[JobType] ) = "General Foreman"  && 'Data for PowerBI'[JobType] ) = "Supervisor" ) )

    RETURN

    DIVIDE ( _1 , _2 , 0 )

     All the best - Theo