Solved: Display Completion Rate (Arrangement of column mat...

LemonKing · ‎08-31-2025

Dear All,

This might be a easy one for you all but no matter how i placed/set the values, im not able to display the completion count in column chart properly.

Staff ID

Name

Department

Training Name

Mandatory

25-Oct

25-Sep

25-Aug

25-Jul

25-Jun

25-May

25-Apr

25-Mar

25-Feb

25-Jan

ID100

John

Sales

Training 6

Yes

Completed

ID101

May

HR

Training 2

Yes

Expired

Registered

ID107

Chris

Finance

Training 2

Yes

Completed

ID103

james

Logistics

Training 1

Yes

Completed

Registered

ID104

Mary

Sales

Training 7

No

Completed

Registered

Expired

Registered

ID105

Gwen

Retail

Training 9

No

Registered

ID106

Vivian

HR

Training 3

Yes

Completed

Registered

ID107

Chris

Finance

Training 10

No

Completed

ID108

Joe

Finance

Training 5

Yes

Completed

Registered

ID109

Cammy

Logistics

Training 9

No

Completed

Registered

ID100

John

Sales

Training 1

Yes

Completed

Registered

ID104

Mary

Sales

Training 7

No

Completed

Expired

Registered

ID107

Chris

Finance

Training 9

No

Completed

Registered

ID103

james

Logistics

Training 2

Yes

Registered

Expired

Registered

ID109

Cammy

Logistics

Training 10

No

Completed

Registered

ID100

John

Sales

Training 8

No

Completed

Expired

Registered

ID105

Gwen

Retail

Training 1

Yes

Completed

Registered

Expired

Registered

ID107

Chris

Finance

Training 8

No

Completed

Registered

ID101

May

HR

Training 1

Yes

Registered

Expired

Registered

ID108

Joe

Finance

Training 3

Yes

Completed

Registered

How can i make this table to show in this format to display only mandatory training completion?

Appreciate if someone can show the DAX for this as majority of my table data is like this.

Thanks in advanced!

FarhanJeelani · ‎08-31-2025

Hi @LemonKing ,

Please follow the below steps:

Data Transformation (Ungrouping)

The best way to handle this is to "unpivot" or "ungroup" your date columns. In Power BI (and similar tools), you can do this in the Power Query Editor:

Select the columns: Select "Staff ID", "Name", "Department", "Training Name", and "Mandatory".
Unpivot Other Columns: Go to the "Transform" tab and click on "Unpivot Other Columns".
Rename Columns: This will create two new columns, typically named "Attribute" (which will contain your dates) and "Value" (which will contain "Completed", "Expired", "Registered", etc.). Rename these to something like "Month" and "Status" for clarity.
After this transformation, your data will look like this:

Staff ID Name Department Training Name Mandatory Month Status
ID100 John Sales Training 6 Yes 25-Oct Completed
ID100 John Sales Training 6 Yes 25-Sep Completed

DAX for Filtering Mandatory Training and Counting Completions

Once your data is in the long format, you can create measures in DAX.

1. Measure to count completions:

Completion Count =
CALCULATE(
COUNTROWS('YourTableName'), -- Replace 'YourTableName' with the actual name of your table
'YourTableName'[Status] = "Completed"
)

2. Filter for Mandatory Training (if you want to show only mandatory)

If you want to display the completion rate only for mandatory training, you can add a filter to your visual or modify the measure:

Filtering the Visual: The easiest way is to put the "Mandatory" column on the filter pane of your visual and select "Yes".

DAX Measure for Mandatory Completions:

Mandatory Completion Count =
CALCULATE(
COUNTROWS('YourTableName'),
'YourTableName'[Status] = "Completed",
'YourTableName'[Mandatory] = "Yes"
)

How to build the chart:

Use a Clustered Column Chart.
X-axis: Drag your "Month" column to the X-axis. Ensure it's treated as a categorical or date axis and sorted correctly.
Y-axis: Drag your Completion Count measure (or Mandatory Completion Count if you filtered for mandatory only) to the Y-axis.

Please mark this post as solution if it helps you. Appreciate Kudos.

View solution in original post

FarhanJeelani · ‎08-31-2025

Hi @LemonKing ,

Please follow the below steps:

Data Transformation (Ungrouping)

The best way to handle this is to "unpivot" or "ungroup" your date columns. In Power BI (and similar tools), you can do this in the Power Query Editor:

Select the columns: Select "Staff ID", "Name", "Department", "Training Name", and "Mandatory".
Unpivot Other Columns: Go to the "Transform" tab and click on "Unpivot Other Columns".
Rename Columns: This will create two new columns, typically named "Attribute" (which will contain your dates) and "Value" (which will contain "Completed", "Expired", "Registered", etc.). Rename these to something like "Month" and "Status" for clarity.
After this transformation, your data will look like this:

Staff ID Name Department Training Name Mandatory Month Status
ID100 John Sales Training 6 Yes 25-Oct Completed
ID100 John Sales Training 6 Yes 25-Sep Completed

DAX for Filtering Mandatory Training and Counting Completions

Once your data is in the long format, you can create measures in DAX.

1. Measure to count completions:

Completion Count =
CALCULATE(
COUNTROWS('YourTableName'), -- Replace 'YourTableName' with the actual name of your table
'YourTableName'[Status] = "Completed"
)

2. Filter for Mandatory Training (if you want to show only mandatory)

If you want to display the completion rate only for mandatory training, you can add a filter to your visual or modify the measure:

Filtering the Visual: The easiest way is to put the "Mandatory" column on the filter pane of your visual and select "Yes".

DAX Measure for Mandatory Completions:

Mandatory Completion Count =
CALCULATE(
COUNTROWS('YourTableName'),
'YourTableName'[Status] = "Completed",
'YourTableName'[Mandatory] = "Yes"
)

How to build the chart:

Use a Clustered Column Chart.
X-axis: Drag your "Month" column to the X-axis. Ensure it's treated as a categorical or date axis and sorted correctly.
Y-axis: Drag your Completion Count measure (or Mandatory Completion Count if you filtered for mandatory only) to the Y-axis.

Please mark this post as solution if it helps you. Appreciate Kudos.

LemonKing · ‎09-01-2025

Thanks @FarhanJeelani

It works!

Can i also ask what if i want to count one additional status ie Completed +Expired?

How should i put the DAX? I tried to add one more liner but its not working.

Mandatory Completion Count =
CALCULATE(
COUNTROWS('YourTableName'),
'YourTableName'[Status] = "Completed",
'YourTableName'[Status] = "Expired"

)

Tried 'YourTableName'[Status] = "Completed" && 'YourTableName'[Status] = "Expired" also not working.

FarhanJeelani · ‎09-01-2025

Hi @LemonKing ,

The issue with your attempts is how you're trying to apply multiple conditions within a single CALCULATE filter context.

When you use 'YourTableName'[Status] = "Completed", 'YourTableName'[Status] = "Expired", it's interpreted as needing a single row to satisfy both conditions simultaneously, which is impossible for a single status value. Similarly, using && (AND) logic means a row must be both "Completed" AND "Expired" at the same time.

To count rows that meet either one condition or another, you need to use the OR operator. In DAX, the OR operator is represented by || or the OR() function.

Below is the correct way to achieve this:

Using || (OR operator)

This is the most concise and common way to achieve the OR logic.

Completed or Expired Count =
CALCULATE(
COUNTROWS('YourTableName'),
'YourTableName'[Status] = "Completed" || 'YourTableName'[Status] = "Expired"
)

Please mark this post as solution if it helps you. Appreciate Kudos.

LemonKing · ‎09-01-2025

Hi @FarhanJeelani

Really thanks for your detailed explaination and help on this.

It cleared all my queries that has been blocking me!

Appreciated!🤗

Display Completion Rate (Arrangement of column matters?)

Helpful resources

Power BI Monthly Update - August 2025

Fabric Community Update - August 2025

Join us at FabCon Vienna from September 15-18, 2025

Display Completion Rate (Arrangement of column matters?)

Helpful resources

Power BI Monthly Update - August 2025

Fabric Community Update - August 2025