Pass Rate

[shantupm5]

Hi All

Please can you help to calculate JL wise Pass rate for the data shared below for each Program name. 

Job Band	Pass/Fail	Program Name
2	Pass	Foundation
2	Pass	Foundation
2	Pass	Foundation
2	Pass	Foundation
2	Pass	Foundation
2	Fail	Foundation
2	Pass	Foundation
2	Pass	Foundation
2	Pass	Foundation
2	Pass	Foundation
2	Pass	Foundation
2	Pass	Foundation
2	Pass	Foundation
2	Pass	Intermdiate
2	Pass	Intermdiate
2	Pass	Intermdiate
2	Pass	Intermdiate
2	Pass	Intermdiate
2	Pass	Intermdiate
2	Pass	Intermdiate
2	Fail	Intermdiate
2	Pass	Intermdiate
2	Pass	Intermdiate
2	Pass	Intermdiate
2	Pass	Intermdiate
2	Pass	Intermdiate
2	Pass	Foundation
2	Pass	Foundation
2	Pass	Foundation
2	Pass	Foundation
2	Pass	Foundation
2	Pass	Foundation
2	Pass	Foundation
2	Pass	Foundation
2	Pass	Foundation
2	Pass	Foundation
2	Pass	Foundation
2	Pass	Foundation
2	Pass	Foundation
2	Pass	Foundation
2	Pass	Foundation
2	Pass	Foundation
2	Pass	Foundation
2	Pass	Foundation
2	Pass	Foundation
2	Pass	Foundation
2	Pass	Foundation
2	Pass	Foundation
2	Pass	Foundation
2	Pass	Foundation
2	Pass	Foundation
2	Pass	Foundation
2	Pass	Foundation
2	Pass	Foundation
2	Pass	Foundation
2	Fail	Foundation
2	Pass	Foundation
2	Pass	Foundation
2	Pass	Foundation
2	Pass	Foundation
2	Pass	Foundation
2	Pass	Foundation
2	Pass	Foundation
2	Pass	Foundation
2	Pass	Foundation
2	Pass	Foundation
2	Pass	Foundation
2	Pass	Foundation
2	Pass	Foundation
2	Pass	Foundation
2	Pass	Professional
2	Pass	Professional
2	Pass	Professional
2	Pass	Professional
2	Pass	Professional
2	Pass	Professional
2	Pass	Professional
2	Pass	Professional
2	Pass	Professional
2	Pass	Professional
2	Pass	Professional
2	Pass	Professional
2	Fail	Professional
2	Pass	Professional
2	Fail	Professional
2	Pass	Professional
2	Pass	Professional
2	Pass	Professional
2	Pass	Professional
2	Pass	Professional
2	Pass	Professional
2	Pass	Professional
2	Pass	Professional
2	Pass	Professional
2	Pass	Professional
2	Pass	Professional
2	Fail	Professional
2	Pass	Professional
2	Pass	Professional
2	Pass	Professional
4	Pass	Professional
4	Pass	Professional
4	Pass	Professional
4	Pass	Professional
4	Pass	Professional
4	Pass	Professional
4	Pass	Professional
4	Pass	Professional
4	Pass	Professional
4	Pass	Professional
4	Pass	Professional
4	Pass	Professional
4	Pass	Professional
4	Fail	Foundation
4	Pass	Foundation
4	Pass	Foundation
4	Pass	Foundation
4	Pass	Foundation
4	Pass	Foundation
4	Fail	Foundation
4	Pass	Foundation
4	Pass	Foundation
4	Pass	Foundation
4	Pass	Professional
4	Pass	Professional
4	Pass	Professional
4	Fail	Professional
4	Pass	Professional
4	Pass	Professional
4	Pass	Professional
4	Pass	Professional
4	Pass	Professional
4	Pass	Professional
4	Pass	Professional
4	Pass	Professional
4	Pass	Professional
4	Pass	Professional
4	Pass	Professional
4	Pass	Professional
4	Pass	Professional
4	Pass	Professional
3	Fail	Professional
3	Pass	Professional
3	Pass	Professional
3	Pass	Professional
3	Pass	Professional
3	Pass	Professional
3	Pass	Professional
3	Pass	Professional
3	Fail	Professional
3	Pass	Professional
3	Pass	Professional
3	Pass	Pro 1
3	Pass	Pro 1
3	Pass	Pro 1
3	Fail	Pro 1
3	Fail	Pro 1
3	Pass	Pro 1
3	Pass	Pro 1
3	Pass	Pro 1

[Ahmedx]

pls try

[DOLEARY85]

Hi,

try this:

Measure = CALCULATE(COUNT('Table'[Pass/Fail]),'Table'[Pass/Fail]="Pass",ALLEXCEPT('Table','Table'[Program Name]))/CALCULATE(COUNT('Table'[Pass/Fail]),ALLEXCEPT('Table','Table'[Program Name]))

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

[shantupm5]

Hello

I applied the logic to actual data set and for some reason the numbers are not accurate. The results are going beyond 100%

The actual numbers should be this,

 But the PowerBI Logic is giving below numbers

Below is the DAXi have used

JL Wise Completion Rate = CALCULATE(COUNT('Training Details Fy 22'[Pass/Fail_1]),'Training Details Fy 22'[Pass/Fail_1]="Pass",ALLEXCEPT('Training Details Fy 22','Training Details Fy 22'[Program Name]))/CALCULATE(COUNT('Training Details Fy 22'[Pass/Fail_1]),ALLEXCEPT('Training Details Fy 22','Training Details Fy 22'[Program Name]))

Please advice

[DOLEARY85]

Ah okay, i wasn't aware that the job band would also be used, you can just adapt the allexcept criteria to use the job band column too.

try removing the all except criteria:

JL Wise Completion Rate = CALCULATE(COUNT('Training Details Fy 22'[Pass/Fail_1]),'Training Details Fy 22'[Pass/Fail_1]="Pass")/CALCULATE(COUNT('Training Details Fy 22'[Pass/Fail_1]))

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

[shantupm5]

Hello 

I tried using the same DAX and still not givig the desired results

[DOLEARY85]

Okay, you could try allexecpt on both program name and job band in case there's something else impacting the measure:

JL Wise Completion Rate = CALCULATE(COUNT('Training Details Fy 22'[Pass/Fail_1]),'Training Details Fy 22'[Pass/Fail_1]="Pass",ALLEXCEPT('Training Details Fy 22','Training Details Fy 22'[Program Name],'Training Details Fy 22'[Job Band]))/CALCULATE(COUNT('Training Details Fy 22'[Pass/Fail_1]),ALLEXCEPT('Training Details Fy 22','Training Details Fy 22'[Program Name],'Training Details Fy 22'[Job Band]))

if this doesn't work are you able to provide a copy of the PBIX file?

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

[shantupm5]

Thanks for all the efforts, but it did not work either.

Do you know how to attach a PBIX here?

[DOLEARY85]

You'd have to upload it to some cloud storage area, make sure access isn't private for the file and paste the link here.

[shantupm5]

Unfortunatley i cant do that due to restricted access 😞