Sum Measure to calculate the total hours required to earn a certificate

[Anonymous]

Hello everyone,

I have a table called "Certification" and there is a part where I need to display how many hours somebody ("JobTitle") needs to spend in order to get a certification. For example, for an engineer to earn the certificate s/he needs to take Course 1 and Course 3, which also means 15 hours need to be spent to complete the courses.

A simple illustration of the table is in the following image:

CourseName	JobTitle	Attendant	Duration(in hours)
Course 1	Engineer	Attendant 1	5
Course 1	Engineer	Attendant 2	5
Course 1	Pharma	Attendant 3	5
Course 2	Art	Attendant 4	3
Course 3	Engineer	Attendant 1	10
Course 3	Engineer	Attendant 2	10
Course 2	Teacher	Attendant 6	3
Course 3	Pharma	Attendant 3	10
Course 1	Teacher	Attendant 5	5

and the output I need to display in Power BI table should be like:

Job Title	Total Hours Spent
Engineer	15
Pharma	15
Art	3
Teacher	8

I've been writing some measures but I always ended up with having same sum values under "Total Hours Spent" column for every "Job Title".

I'd appreciate if you could help me with that.

Thank you in advance

[grantsamborn]

Hi @Anonymous 

Would something like this help?

Total Hours Spent = 
CALCULATE(
    SUM( 'DataTable'[Duration (Hours)] ),
    ALLSELECTED( 'DataTable'[CourseName] )
)

[Anonymous]

Hello,

Thank you for your answer but I just realised that there was a missing part on the sample illustration I attached. The things is there are many attendants are taking these classes therefore "CourseName" column has many duplicated strings inside. Otherwise, yes, your solution work.

I wanted to use FILTER but then I'm loosing other JobTitles (i.e a teacher and an engineer might take the same class) and their "Duration(in Hours" values.

[grantsamborn]

Hi

Can you repost data that reflects your new requirement?

Also, can you post it in a format that doesn't require retyping.

https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

[Anonymous]

I just editted in the way you suggested

[grantsamborn]

Next, I calculated the number of attendants per course and use that in a final measure.

Total Hours Spent = 
CALCULATE(
    SUM( 'DataTable'[Duration] ),
    ALLSELECTED( 'DataTable'[CourseName] )
)

Attendants = 
CALCULATE(
    DISTINCTCOUNT( 'DataTable'[Attendant] ),
    ALLSELECTED( 'DataTable'[CourseName] )
)

zMeasure = DIVIDE( [Total Hours Spent], [Attendants] )

This works for most except Teachers since there are 2 different attendants that took 1 class each (different classes).

I'm not sure how you would want to calculate it if different attendants have taken different numbers of classes.  (How can I determine if all classes have been taken?)

I hope this makes sense.

[Anonymous]

It unfortunatelly didn't work. For example it still calculating Course 1 and Course 3 for engineers twice.
And this yields to meaning like "an engineer must spend 30 hours" ( 2xCourse 1= 10 hours and 2xCourse 3 lasting 20 hours)
but instead, the output should mean that "an engineer must spend 15 hours" (The course1 lasting 5 hours and the course3 lasting 10 hours)

[grantsamborn · Accepted solution]

Hi @Anonymous 

Sorry.  I think I was going in the wrong direction.

Try this:

try3 = 
SUMX(
    SUMMARIZE(
        'DataTable',
        'DataTable'[JobTitle],
        'DataTable'[CourseName],
        "__Duration",
            MAX( 'DataTable'[Duration] )
    ),
   [__Duration]
)

[Anonymous]

Yeah it almost worked. It's just that when an attendant name is redundant in JobType then it miscalculates, otherwise it's fine For example if an attendant took course1 and course3 and matches to "Engineer" in JobPosition column.

[grantsamborn]

Can you show data that reflects this example?

What I was doing was taking the MAX of the duration for each combination [JobTitle] and [CourseName] to calculate the duration for the course. 

• This making two assumptions:
  • A) that the [Duration] is the same regardless of [Job Title]
  • B) that since the [Duration] *should* be the same, using MAX should work.  (or MIN, AVERAGE, etc.)

Once I have that “nominal” amount for each [CourseName], I can SUM [Duration] for each [JobTitle].

To go any further, I’ll need data that reflects the example you’re referring to.

[Anonymous]

I tried again and it worked on the dummy data I prepared to practice.

Thank you Grant