Forum Discussion
Filtered Measure help
Hello,
I'm really new to BI and need some assistance to see if what I want to create is possible.
I want to create some of type of function, measure, or count (using info detailed below) to identify the number of people with Position Code 5 who only have a training status of R and Q. I would then like to take that count and divide by the total number of Position Code 5s to obtain a qualification percentage. If there are resources or vidoes I should look at that would be helpful as well.
Thank you
| Name | Training Status | Position Code |
| Bob | A | 5 |
| Joel | D | 7 |
| Peter | C | 7 |
| Nate | R | 5 |
| Dave | D | 7 |
| Gary | Q | 5 |
| Draco | Q | 5 |
Lucian | R | 5 |
| Elrond | A | 5 |
Create the Measure for the Count of People with Position Code 5 and Training Status R or Q:
Use a DAX measure to filter the table and count only those people with Position Code = 5 and Training Status = "R" or Training Status = "Q".
Qualified_Count = CALCULATE( COUNTROWS(YourTableName), YourTableName[Position Code] = 5, YourTableName[Training Status] IN {"R", "Q"} )Create a Measure for the Total Number of People with Position Code 5:
This measure will count the total number of people with Position Code = 5.
Total_PositionCode5_Count = CALCULATE( COUNTROWS(YourTableName), YourTableName[Position Code] = 5 )Create a Measure for the Qualification Percentage:
Now, divide the count of qualified people by the total number of people with Position Code = 5 to get the percentage.
Qualification_Percentage = DIVIDE( [Qualified_Count], [Total_PositionCode5_Count], 0 )
3 Replies
Create the Measure for the Count of People with Position Code 5 and Training Status R or Q:
Use a DAX measure to filter the table and count only those people with Position Code = 5 and Training Status = "R" or Training Status = "Q".
Qualified_Count = CALCULATE( COUNTROWS(YourTableName), YourTableName[Position Code] = 5, YourTableName[Training Status] IN {"R", "Q"} )Create a Measure for the Total Number of People with Position Code 5:
This measure will count the total number of people with Position Code = 5.
Total_PositionCode5_Count = CALCULATE( COUNTROWS(YourTableName), YourTableName[Position Code] = 5 )Create a Measure for the Qualification Percentage:
Now, divide the count of qualified people by the total number of people with Position Code = 5 to get the percentage.
Qualification_Percentage = DIVIDE( [Qualified_Count], [Total_PositionCode5_Count], 0 )
Thank you - worked perfectly and taught me something new!
Hi,
Based on the table which you have shared, show the expected result.
