Emp code wise completion Rate

[shantu_pm5]

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

[Anonymous · Accepted solution]

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]

@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]

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

[Anonymous]

@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 )

[Nithinr]

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

[shantu_pm5]

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

[Nithinr]

• If they are dax you would have already having some other column where your are fetching completed status instead of having a complex dax statement you can get completed count from there itself , what calculation are you using to get complete status? 

[shantu_pm5]

I have used lookup function. So there are two data set with employee id as commo. Each emp id has training status as completed. So I have fetched that as a column in existing table 

[Nithinr]

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]))