Forum Discussion

b-kopik's avatar
b-kopik
Icon for Helper III rankHelper III
6 months ago
Solved

Best Data Model for Headcount (Monthly) and Course Data in Power BI

I’m working with two datasets in Power BI that both contain multiple rows per employee, but represent different types of information. Dataset 1: Combined Headcount Data Every month, I l...
Need Help
  • v-hashadapu's avatar
    v-hashadapu
    4 months ago

    Hi b-kopik ,

    If you want to count how many courses were taken, try something like:

    CoursesTaken :=
    CALCULATE (
    COUNTROWS ( 'Campus Data' ),
    TREATAS (
    VALUES ( 'Combined Headcount'[Employee ID] ),
    'Campus Data'[Employee ID]
    )
    )

    or if you want number of employees trained, try something like:

    EmployeesTrained :=
    CALCULATE (
    DISTINCTCOUNT ( 'Campus Data'[Employee ID] ),
    TREATAS (
    VALUES ( 'Combined Headcount'[Employee ID] ),
    'Campus Data'[Employee ID]
    )
    )

b-kopik's avatar
b-kopik
Icon for Helper III rankHelper III
6 months ago

Thanks, I am having trouble with the Age metric. I tried both formulas and they didn't work:

 

Option 1:

 

AvgAgeTraining =
CALCULATE(
AVERAGE('Combined Headcount'[Age]),
'Campus Data'
)

 

Option 2:

 

 AvgAgeTraining =

CALCULATE(AVERAGE('Combined Headcount'[Age]), 'Campus Data') 

 

 

I would like for it to only calculate the average age of those employees who attended the training - but the age column is in the combined headcount dataset

johnt75's avatar
johnt75
Icon for Super User rankSuper User
6 months ago

You could try

AvgAgeTraining =
VAR EmployeesWithTraining =
    CALCULATETABLE ( VALUES ( 'Campus Data'[Employee ID] ) )
VAR Result =
    CALCULATE (
        AVERAGE ( 'Combined Headcount'[Age] ),
        TREATAS ( EmployeesWithTraining, Employee[Employee ID] )
    )
RETURN
    Result