Forum Discussion

shantupm5's avatar
shantupm5
Icon for Helper III rankHelper III
2 years ago

compliance rate

Hello

 

Please can you help write a measure to calculate compliance rate for each job level from the data below

 

Regards

Prashanth

 

Job LevelTraining 1Training 2Training 3Training 4Training 5Training 6
1Compliant Compliant  Compliant
2 Compliant    
3Compliant  CompliantCompliant 
4 Compliant  Compliant 
5Compliant   Compliant 
6 Compliant    
7   Compliant Compliant
Need Help
Show and Tell
Tips and Tricks

2 Replies

  • DOLEARY85's avatar
    DOLEARY85
    Icon for Resident Rockstar rankResident Rockstar
    2 years ago

    Hi,

     

    you can do this in power query by transforming the table, 

     

    1. Unpivot all the training columns

    2. Then group by job level and value - use aggregation count distict rows

    3. Pivot the Count column with aggregation sum

    4. Rename blank column to Non Compliant

    5. Add a custom column to calculate the % of the compliant column against the total of complaint and non complaint columns

    6. Change the data type to percentage

     

    If you need the original data structure you could create a copy of the table in power query befroe performing these steps.

     

    I've attached a PBIX file for you to review the steps above.

     

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

  • DOLEARY85's avatar
    DOLEARY85
    Icon for Resident Rockstar rankResident Rockstar
    2 years ago

    Alternatively if it needs to be a measure you could do something like the below, however it's not as dynamic:

     

    Measure =

    Var Compliant = CALCULATE(COUNT('Table'[Training 1]),'Table'[Training 1]="Compliant")+CALCULATE(COUNT('Table'[Training 2]),'Table'[Training 2]="Compliant")+CALCULATE(COUNT('Table'[Training 3]),'Table'[Training 3]="Compliant")+CALCULATE(COUNT('Table'[Training 4]),'Table'[Training 4]="Compliant")+CALCULATE(COUNT('Table'[Training 5]),'Table'[Training 5]="Compliant")+CALCULATE(COUNT('Table'[Training 6]),'Table'[Training 6]="Compliant")

    Var Non_Compliant = CALCULATE(COUNT('Table'[Training 1]),'Table'[Training 1]="")+CALCULATE(COUNT('Table'[Training 2]),'Table'[Training 2]="")+CALCULATE(COUNT('Table'[Training 3]),'Table'[Training 3]="")+CALCULATE(COUNT('Table'[Training 4]),'Table'[Training 4]="")+CALCULATE(COUNT('Table'[Training 5]),'Table'[Training 5]="")+CALCULATE(COUNT('Table'[Training 6]),'Table'[Training 6]="")

    Return

    Compliant/(Non_Compliant+Compliant)
     
     
    If I answered your question, please mark my post as solution, Appreciate your Kudos 👍