How to add values in one cell based on values in 2 others

[mooneyalan4]

Thanks in advance to anyone who takes the time to read this, and more to anyone who can help.

I'm trying to put together a curriculum status dashboard for a group of employees. Before I get too deep into collating all the data in Excel, I'm having an issue in PowerBi where I cannot figure out how to do this.

What I need to do is:

• Track the status of a group of employees training modules
• In the example below they are called Employee A, B, C, etc..
• They are all completing a variety of training modules: called Module A, B, C,
• Each module takes a specific length of time to complete. Hours row.
• Each employee can have a status of: Not Required | Required | Enrolled | Absent | Complete for each training module.
• I need totalled columns for each employee: (similar to columns L & M)
  • Planned = Required + Enrolled + Absent + Complete
  • Actual = Complete
  • Absent = Absent
  • Scheduled = Enrolled

Its possible the formatting of my data isnt right for this, I dont mind changing it at this early stage.

Appreciate if anyone can help with this.

Table and Screenshot below.

Name

Module A

Module B

Module C

Module D

Module E

Module F

Module G

Module H

Module I

Planned

Actual

Hours

2

2

2

4

4

4

8

8

16

Employee 1

Enrolled

Enrolled

Completed

Enrolled

Enrolled

Completed

Completed

Completed

Completed

50

38

Employee 2

Enrolled

Enrolled

Completed

Not Required

Not Required

Not Required

Completed

Absent

Absent

38

14

Employee 3

Completed

Enrolled

Completed

Enrolled

Enrolled

Completed

Completed

Completed

Completed

50

40

Employee 4

Absent

Enrolled

Completed

Not Required

Enrolled

Not Required

Completed

Completed

Completed

42

34

Employee 5

Enrolled

Enrolled

Absent

Enrolled

Enrolled

Completed

Completed

Completed

Completed

50

36

Employee 6

Completed

Enrolled

Not Required

Enrolled

Enrolled

Not Required

Completed

Completed

Absent

44

18

Employee 7

Absent

Enrolled

Completed

Not Required

Enrolled

Not Required

Not Required

Completed

Completed

34

26

Employee 8

Absent

Enrolled

Completed

Enrolled

Enrolled

Completed

Absent

Completed

Absent

50

14

Employee 9

Completed

Enrolled

Completed

Enrolled

Enrolled

Absent

Completed

Completed

Completed

50

36

Employee 10

Not Required

Not Required

Not Required

Not Required

Not Required

Completed

Completed

Completed

Completed

36

36

Employee 11

Completed

Enrolled

Not Required

Not Required

Not Required

Not Required

Completed

Completed

Completed

36

34

Employee 12

Completed

Enrolled

Completed

Not Required

Enrolled

Completed

Not Required

Completed

Completed

46

32

Employee 13

Completed

Enrolled

Completed

Enrolled

Not Required

Not Required

Completed

Completed

Completed

42

36

[Bmejia · Accepted solution]

The data structure is base on what works in your situation.  If the data structure is setup in this manner already where the module types are in different columns then you can add a new columns like these. 

PLANNED =

VAR MA = IF(yourTableNotPivot[Module A]<>"Not Required",2,0)

VAR MB = IF(yourTableNotPivot[Module B]<>"Not Required",2,0)

VAR MC = IF(YourTableNotPivot[Module C]<>"Not Required",2,0)

VAR MD = IF(yourTableNotPivot[Module D]<>"Not Required",4,0)

VAR ME = IF(yourTableNotPivot[Module E]<>"Not Required",4,0)

VAR MF = IF(YourTableNotPivot[Module F]<>"Not Required",4,0)

VAR MG = IF(yourTableNotPivot[Module G]<>"Not Required",8,0)

VAR MH = IF(yourTableNotPivot[Module H]<>"Not Required",8,0)

VAR MI = IF(YourTableNotPivot[Module I]<>"Not Required",16,0)

Return MA+MB+MC+MD+ME+MF+MG+MH+MI

---- For Actuals and other colums you can create them something like this, update completed to what ever status you need to calculate

ACTUAL =

VAR MA = IF(yourTableNotPivot[Module A]="Completed",2,0)

VAR MB = IF(yourTableNotPivot[Module B]="Completed",2,0)

VAR MC = IF(YourTableNotPivot[Module C]="Completed",2,0)

VAR MD = IF(yourTableNotPivot[Module D]="Completed",4,0)

VAR ME = IF(yourTableNotPivot[Module E]="Completed",4,0)

VAR MF = IF(YourTableNotPivot[Module F]="Completed",4,0)

VAR MG = IF(yourTableNotPivot[Module G]="Completed",8,0)

VAR MH = IF(yourTableNotPivot[Module H]="Completed",8,0)

VAR MI = IF(YourTableNotPivot[Module I]="Completed",16,0)

return MA+MB+MC+MD+ME+MF+MG+MH+MI