Forum Discussion

shantupm5's avatar
shantupm5
Helper III
2 years ago

Pass Rate

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

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's avatar
shantupm5
Helper III
2 years ago

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's avatar
    DOLEARY85
    Resident Rockstar
    2 years ago

    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's avatar
      shantupm5
      Helper III
      2 years ago

      Hello 

       

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

       

       

      • DOLEARY85's avatar
        DOLEARY85
        Resident Rockstar
        2 years ago

        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 👍