Forum Discussion
Emp code wise completion Rate
Hi Team
I have below data set and i am looking for employee was training completion rate.
Ex: Emp code 1 has completed 3 courses out of 6 which should be 50%.
| Emp Code | Training Module 1 | Training Module 2 | Training Module 3 | Training Module 4 | Training Module 5 | Training Module 6 |
| 1 | Completed | Completed | Completed | |||
| 2 | Completed | |||||
| 3 | Completed | Completed | ||||
| 4 | Completed | Completed | Completed | Completed | Completed | Completed |
| 5 | ||||||
| 6 | Completed | Completed | Completed |
Hi shantu_pm5 ,
Here are the steps you can follow：
1. Create calculated table.
Table 2 = UNION( SELECTCOLUMNS( 'Table',"Emp Code",'Table'[Emp Code],"Training Module","Training Module 1","Value",[Training Module 1]), SELECTCOLUMNS( 'Table',"Emp Code",'Table'[Emp Code],"Training Module","Training Module 2","Value",[Training Module 2]), SELECTCOLUMNS( 'Table',"Emp Code",'Table'[Emp Code],"Training Module","Training Module 3","Value",[Training Module 3]), SELECTCOLUMNS( 'Table',"Emp Code",'Table'[Emp Code],"Training Module","Training Module 4","Value",[Training Module 4]), SELECTCOLUMNS( 'Table',"Emp Code",'Table'[Emp Code],"Training Module","Training Module 5","Value",[Training Module 5]), SELECTCOLUMNS( 'Table',"Emp Code",'Table'[Emp Code],"Training Module","Training Module 6","Value",[Training Module 6]))2. Create measure.
Test = var _countnotblank= COUNTX( FILTER(ALL('Table 2'),'Table 2'[Emp Code]=MAX('Table 2'[Emp Code])&&'Table 2'[Value]<>BLANK()), [Value]) var _count= COUNTX( FILTER(ALL('Table 2'),'Table 2'[Emp Code]=MAX('Table 2'[Emp Code])), [Value]) return IF( MAX('Table 2'[Value])=BLANK(),BLANK(), DIVIDE( _countnotblank,_count))3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
9 Replies
open power query editor --> right click on "emp code" --> select 'unpivot other columns'
you see the data like below
Create measure
Measure = DIVIDE(CALCULATE(COUNT('Table'[Value]),'Table'[Value]="Completed"),count('Table'[Attribute]))shantu_pm5 Please try this:
- In the Query Editor, select all the Training Module columns- Within the Transform ribbon, select Replace Values, and replace the nulls with Incomplete
- With all the Transform columns selected, select Unpivot columns within the Transform tab- Rename Attribute to Training Module and Value to Status
- Click Close & Apply
- Create the following Measure and bring it in to a table visualization and change its format to Percentage. It stores the number of training modules available and the number of completed modules in variables, then divides the number completed by the number of training modules available in another variable. It then returns the percent complete or a 0 if no training modules have been completed.
Percent Complete =
VAR _count =
DISTINCTCOUNT ( Sheet1[Training Module] )
VAR _status =
CALCULATE (
COUNT ( Sheet1[Status] ),
KEEPFILTERS ( Sheet1[Status] = "Completed" )
)
VAR _complete =
DIVIDE ( _status, _count )
RETURN
IF ( NOT ( ISBLANK ( _complete ) ), _complete, 0 )Hello
The training modules i have mentioned above are columns with DAX. I am not able to see them in Power Editor.
Please advice
Regards
Is it possible to send pbix file that you are using in one drive/ google share
Hi shantu_pm5 ,
Here are the steps you can follow：
1. Create calculated table.
Table 2 = UNION( SELECTCOLUMNS( 'Table',"Emp Code",'Table'[Emp Code],"Training Module","Training Module 1","Value",[Training Module 1]), SELECTCOLUMNS( 'Table',"Emp Code",'Table'[Emp Code],"Training Module","Training Module 2","Value",[Training Module 2]), SELECTCOLUMNS( 'Table',"Emp Code",'Table'[Emp Code],"Training Module","Training Module 3","Value",[Training Module 3]), SELECTCOLUMNS( 'Table',"Emp Code",'Table'[Emp Code],"Training Module","Training Module 4","Value",[Training Module 4]), SELECTCOLUMNS( 'Table',"Emp Code",'Table'[Emp Code],"Training Module","Training Module 5","Value",[Training Module 5]), SELECTCOLUMNS( 'Table',"Emp Code",'Table'[Emp Code],"Training Module","Training Module 6","Value",[Training Module 6]))2. Create measure.
Test = var _countnotblank= COUNTX( FILTER(ALL('Table 2'),'Table 2'[Emp Code]=MAX('Table 2'[Emp Code])&&'Table 2'[Value]<>BLANK()), [Value]) var _count= COUNTX( FILTER(ALL('Table 2'),'Table 2'[Emp Code]=MAX('Table 2'[Emp Code])), [Value]) return IF( MAX('Table 2'[Value])=BLANK(),BLANK(), DIVIDE( _countnotblank,_count))3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
