cancel
Showing results for
Search instead for
Did you mean:
Regular Visitor

## percentage of column with text values

Hi there

Probably an easy one for the seasoned PowerBi developer. I'm a newbie.

we looking to find percentage for column status where value is "OnTime". I already have a table for date dimension which we'll use to visualize data by month

 Date Status 01-Mar OnTime 04-Mar Late 04-Apr Undetermined 07-Apr Late 05-Jun OnTime 08-Jul OnTime 01-Aug Late 03-Aug Undetermined
2 ACCEPTED SOLUTIONS
Solution Sage

Hello,

You can use this DAX measure to achieve it:

``%OnTimeStatus = DIVIDE(CALCULATE(COUNTA(StatusTable[Status]),FILTER(StatusTable,StatusTable[Status] = "OnTime")),CALCULATE(COUNTA(StatusTable[Status]),ALLEXCEPT(StatusTable,StatusTable[Status])))``

Also ensure to click on the measure & change the format to percentage.

This will give you the percentage of On Time on the basis of total rows in your table.

I hope I interpreted your requirement correctly.

Please respond back if you are looking for something else

Solution Sage

Sure you can. Both will work for you.

Infact COUNTROWS will give more efficient result for you.

9 REPLIES 9
Solution Sage

Hello,

You can use this DAX measure to achieve it:

``%OnTimeStatus = DIVIDE(CALCULATE(COUNTA(StatusTable[Status]),FILTER(StatusTable,StatusTable[Status] = "OnTime")),CALCULATE(COUNTA(StatusTable[Status]),ALLEXCEPT(StatusTable,StatusTable[Status])))``

Also ensure to click on the measure & change the format to percentage.

This will give you the percentage of On Time on the basis of total rows in your table.

I hope I interpreted your requirement correctly.

Please respond back if you are looking for something else

Regular Visitor

Could i have used

``````%OnTimeStatus = DIVIDE(CALCULATE(COUNTROWS(StatusTable),StatusTable[Status] = "OnTime"),CALCULATE(COUNTROWS(StatusTable)))

THis seems to give me a different result which looks more accurate tho``````
Solution Sage

Sure you can. Both will work for you.

Infact COUNTROWS will give more efficient result for you.

Frequent Visitor

This has been super useful to a problem I am trying to tackle as well however I need to exclude the "blanks" in this column prior to the calculation. How would I edit the measure to achieve this?

Solution Sage

You can use COUNTROWS DAX function with an expression. Something like below:

``%OnTimeStatus = DIVIDE(CALCULATE(COUNTROWS(FILTER(StatusTable,StatusColumn <> null)),StatusTable[Status] = "OnTime"),CALCULATE(COUNTROWS(StatusTable)))``

Used:

(COUNTROWS(FILTER(StatusTable,StatusColumn <> null))

Frequent Visitor
I have given that a go however receive the error message "operator or expression 'NULL' is not supported in this context. I have included the measure I have used below.

"Psych Data" is my table name, and "Appointment Required" is my column in which I want to work out how many are "Yes".

Measure = DIVIDE(CALCULATE(COUNTROWS(FILTER('Psych data','Psych data'[Appointment required]<>null)),'Psych data'[Appointment required]="Yes"),CALCULATE(COUNTROWS('Psych data')))
Solution Sage

Try:

Measure = DIVIDE(CALCULATE(COUNTROWS(FILTER('Psych data','Psych data'[Appointment required]<> BLANK())),'Psych data'[Appointment required]="Yes"),CALCULATE(COUNTROWS('Psych data')))

Frequent Visitor

That's resulted in the same figure unfortunately.

Thankfully though I have managed to sort it; with some help from a work colleague. I really appreciate you taking the time though.

Regular Visitor

Thanx PC2790

## Helpful resources

Announcements

#### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

#### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

#### Claim Your Badge & Digital Swag!

Check out how to claim yours today!

#### Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors
Top Kudoed Authors