How to show status of the training completion by User Name

[DavidGM]

key	User	Manager	BI	CD	Tool	PO	DB	Trarining Complete
1	Alex	Shane	Y	Y	Y	Y	Y	Y
2	Angela	Shane	Y	Y	Y	Y	Y	Y
3	Andrew	Shane	Y	Y	Y	Y	N	N
4	Jamie	Chris	Y	Y	Y	N	Y	N
5	Joseph	Chris	N	N	N	N	N	N
6	Smith	Katherine	Y - PASS	Y	Y - PASS	Y	Y	Y
7	Bravo	Katherine	Y	Y	Y	Y	Y	Y
8	Dean	Lee	Y	Y	Y	Y	Y	Y
9	John	Lee	Y	Y	Y	Y	Y	Y
10	William	Lee	Y	Y	Y	Y	N	N

Training Complete Excel formula= =IF(COUNTIF(D2:H2,"")>0,"N",IF(COUNTIF(D2:H2,"N")>0,"N","Y")) for all the rows in Training Complete.

Problem:  I like to show in PowerBI the status of the training completion by user name, report should show completion status of training complete and overall % complete. I like to filter on the Manager

Thanks in advance team.

[MahyarTF]

Hi,

You could create the calculated columns as below :

- for Training Status :

Training Status Y/N = if(UPPER(left(trim(Sheet217[BI]),1)) & UPPER(left(trim(Sheet217[CD]),1)) & UPPER(left(trim(Sheet217[Tool]),1)) &

                      UPPER(left(trim(Sheet217[PO]),1)) & UPPER(left(trim(Sheet217[DB]),1)) = "YYYYY",

                      "Y",

                      "N"

                    )

- For Training Passing Ratio :

Training % =

Var BIStatus = if( UPPER(left(trim(Sheet217[BI]),1)) = "Y", 1, 0)

Var CDStatus = If( UPPER(left(trim(Sheet217[CD]),1)) = "Y", 1, 0)

Var ToolStatus = If( UPPER(left(trim(Sheet217[Tool]),1)) = "Y", 1, 0)

Var POStatus = If( UPPER(left(trim(Sheet217[PO]),1)) = "Y", 1, 0)

Var DBStatus = If( UPPER(left(trim(Sheet217[DB]),1)) = "Y", 1, 0)

Var Ration = DIVIDE( (BIStatus + CDStatus + ToolStatus + POStatus + DBStatus), 5)

return Ration

Thanks for Kudos,

Please mark it as solution if it helps

[DavidGM]

Thanks, 

like to understand a bit more about the UPPER(left(trim(sheet217[BI], as BI I can understand it the the table but the rest of the query.. 

[DavidGM]

SHEET217 ? Is a bit confusing 

[danextian]

Hi @DavidGM ,

For this, I would unpivot the data first before making any calculations. Once unpivoted, I can then count the number of N per user and then use the count in calculating for the percentage of completion.

Here are the measures I'd use:

Count of Incomplete = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER ( ALLEXCEPT ( 'Table', 'Table'[EY #] ), 'Table'[Value] = "N" )
)

Progress % = 
1 - DIVIDE ( [Count of Incomplete], 6 )

Training Status = 
IF ( [Count of Incomplete] = BLANK (), "Y", "N" )

Sample result:

Please see attached pbix for the details.

[DavidGM · Accepted solution]

It worked well for me! Thanks