Forum Discussion

shantu_pm5's avatar
shantu_pm5
New Member
2 years ago
Solved

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...
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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 )

 

 

 

  • shantu_pm5's avatar
    shantu_pm5
    New Member
    2 years ago

    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

     

    • Nithinr's avatar
      Nithinr
      Resolver III
      2 years ago

      Is it possible to send pbix file that you are using in one drive/ google share 

      • shantu_pm5's avatar
        shantu_pm5
        New Member
        2 years ago

        Unfortunately I will not be able to as we have customer confident Data set

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      shantu_pm5  In that case, please try using the Number Complete measure to calculate the number of completed trainings, and the Percent Complete measure below it to calculate the percent complete. This approach would require maintenance if the number of trainings changes.

      Number Complete =
      VAR _count1 =
          CALCULATE (
              COUNTA ( Sheet1[Training Module 1] ),
              ALLEXCEPT ( Sheet1, Sheet1[Emp Code] )
          )
      VAR _count2 =
          CALCULATE (
              COUNTA ( Sheet1[Training Module 2] ),
              ALLEXCEPT ( Sheet1, Sheet1[Emp Code] )
          )
      VAR _count3 =
          CALCULATE (
              COUNTA ( Sheet1[Training Module 3] ),
              ALLEXCEPT ( Sheet1, Sheet1[Emp Code] )
          )
      VAR _count4 =
          CALCULATE (
              COUNTA ( Sheet1[Training Module 4] ),
              ALLEXCEPT ( Sheet1, Sheet1[Emp Code] )
          )
      VAR _count5 =
          CALCULATE (
              COUNTA ( Sheet1[Training Module 5] ),
              ALLEXCEPT ( Sheet1, Sheet1[Emp Code] )
          )
      VAR _count6 =
          CALCULATE (
              COUNTA ( Sheet1[Training Module 6] ),
              ALLEXCEPT ( Sheet1, Sheet1[Emp Code] )
          )
      RETURN
          _count1 + _count2 + _count3 + _count4 + _count5 + _count6

      Percent Complete =
      VAR _complete =
          DIVIDE ( [Number Complete], 6 )
      RETURN
          IF ( NOT ( ISBLANK ( _complete ) ), _complete, 0 )