Joblevel wise Training Module wise Training completion Rate

[shantupm5]

Hello Everyone, 

I have a complex requirment which i am unable to resolve. Basicall i have 3 data set which is from 3 different source (Tables)

Source 1) Employee Id along with their Job Level

Source 2) Mandatory Training Module for each Job Level

Source 3) List of Employee Id's along with the Job Level who have completed 1 or more training from the Mandatory training Module list. 

Date set is attached below.

Requirement is, we are looking for Job Level wise, Mandatory training module wise completion rate. Please can you resolve and share this in PBIX format which will be of more help.

Data set: 

Source 1) Employee Id along with their Job Level

Emp Id	Job Level
100	2
200	3
300	2
400	2
500	3
600	4
700	3
800	3
900	4
1000	3
1100	3
1200	3
1300	3
1400	3
1500	4

Source 2) Mandatory Training Module for each Job Level

JL	Mandatory Training Module
2	Foundation - 2
2	Intermediate - 2
2	Pro - 2
3	Foundation - 3
3	Intermediate - 3
3	Pro - 3
4	Foundation - 4
4	Intermediate - 4
4	Pro - 4

Source 3) List of Employee Id's along with the Job Level who have completed 1 or more training from the Mandatory training Module list. 

Emp Id	JL	Training Module Completed
100	2	Foundation - 2
100	2	Intermediate - 2
100	2	Pro - 2
200	3	Foundation - 3
200	3	Intermediate - 3
300	2	Foundation - 2
400	2	Foundation - 2
400	2	Intermediate - 2
500	3	Foundation - 3
500	3	Intermediate - 3
600	4	Foundation - 4
700	3	Foundation - 3
800	3	Foundation - 3
800	3	Intermediate - 3
800	3	Pro - 3
900	4	Foundation - 4
900	4	Intermediate - 4
1000	3	Foundation - 3
1000	3	Intermediate - 3
1000	3	Pro - 3
1100	3
1200	3
1300	3
1400	3
1500	4

Thanks in advance

[v-yaningy-msft · Accepted solution]

Hi, shantupm5

May I ask if this is the expected output you are looking for? Based on your description, I have created many measures to achieve the effect you are looking for. Following picture shows the effect of the display.

Measures:

CompletedRate =

VAR _dist =

    DISTINCTCOUNT ( 'Source 2'[Mandatory Training Module] )

VAR _empNumbers =

    CALCULATE (

        DISTINCTCOUNT ( 'Source 3'[Emp Id] ),

        'Source 3'[JL] IN ALLSELECTED ( JL[JL] )

    )

VAR _allCounts =

    CALCULATE (

        COUNTROWS ( 'Source 3' ) / _dist,

        FILTER ( 'Source 3', [Counts] = _dist )

    )

VAR _completedRate =

    DIVIDE ( _allCounts, _empNumbers )

RETURN

    _completedRate



Counts =

VAR _Counts =

    COUNTAX (

        FILTER (

            ALL ( 'Source 3' ),

            'Source 3'[Emp Id] = SELECTEDVALUE ( 'Source 3'[Emp Id] )

        ),

        'Source 3'[Emp Id]

    )

RETURN

    _Counts

If this does not work, could you please share some sample data without sensitive information and expected output.
How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Fowmy · Accepted solution]

@shantupm5 

I have already answered your question in another thread: Solution Requested - Microsoft Fabric Community
Pease accept it if it works for you, 

[v-yaningy-msft · Accepted solution]

Hi, shantupm5

May I ask if this is the expected output you are looking for? Based on your description, I have created many measures to achieve the effect you are looking for. Following picture shows the effect of the display.

Measures:

CompletedRate =

VAR _dist =

    DISTINCTCOUNT ( 'Source 2'[Mandatory Training Module] )

VAR _empNumbers =

    CALCULATE (

        DISTINCTCOUNT ( 'Source 3'[Emp Id] ),

        'Source 3'[JL] IN ALLSELECTED ( JL[JL] )

    )

VAR _allCounts =

    CALCULATE (

        COUNTROWS ( 'Source 3' ) / _dist,

        FILTER ( 'Source 3', [Counts] = _dist )

    )

VAR _completedRate =

    DIVIDE ( _allCounts, _empNumbers )

RETURN

    _completedRate



Counts =

VAR _Counts =

    COUNTAX (

        FILTER (

            ALL ( 'Source 3' ),

            'Source 3'[Emp Id] = SELECTEDVALUE ( 'Source 3'[Emp Id] )

        ),

        'Source 3'[Emp Id]

    )

RETURN

    _Counts

If this does not work, could you please share some sample data without sensitive information and expected output.
How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum