percentage of column with text values

[Anonymous]

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

[PC2790 · Accepted solution]

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 

[PC2790 · Accepted solution]

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 

[Anonymous]

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

[PC2790 · Accepted solution]

Sure you can. Both will work for you.

Infact COUNTROWS will give more efficient result for you.

[FW94]

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]

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]

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]

Try:

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

[FW94]

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.

[Anonymous]

Thanx PC2790