Reply
shantupm5
Helper III
Helper III
Partially syndicated - Outbound

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
2 REPLIES 2
DOLEARY85
Resident Rockstar
Resident Rockstar

Syndicated - Outbound

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 👍
DOLEARY85
Resident Rockstar
Resident Rockstar

Syndicated - Outbound

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 👍

avatar user

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)