cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
thabitisaacs
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

 

DateStatus
01-MarOnTime
04-MarLate
04-AprUndetermined
07-AprLate
05-JunOnTime
08-JulOnTime
01-AugLate
03-AugUndetermined
2 ACCEPTED SOLUTIONS
PC2790
Solution Sage
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 

View solution in original post

Sure you can. Both will work for you.

Infact COUNTROWS will give more efficient result for you.

View solution in original post

9 REPLIES 9
PC2790
Solution Sage
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 

View solution in original post

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

Sure you can. Both will work for you.

Infact COUNTROWS will give more efficient result for you.

View solution in original post

FW94
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?

PC2790
Solution Sage
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))

FW94
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')))
PC2790
Solution Sage
Solution Sage

Try:

 

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

FW94
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.

Thanx PC2790

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

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

MBAS on Demand

Microsoft Business Applications Summit sessions

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

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors