Return % based on COUNTIF

[simonrtaylor]

Hi, I've haven't found this exact question asked elsewhere and hope you can help.

I have two columns and want to produce the equivalent of a COUNTIF formatted as a percentage:

Target  Date - date that action should have been completed.

Action Date - when actioned, or NULL

KPI - result can be "Inside", "Outside" or "Pending" [returned via MySQL]

The measure would need to be (number of rows where KPI is "Inside"/number of rows with a target date)*100. I'd then use the filters on the visualisation to filter the target date period to show, e.g. the % of target dates in the last 7 days that have been achieved.

Thanks in advance!

[Anonymous · Accepted solution]

-- You should have a proper Date table
-- linked to T[Target Date] and slice
-- by its attributes. T[Target Date]
-- should be a hidden column in T.

[Measure] =
var __inside =
	CALCULATE(
		COUNTROWS( T ),
		KEEPFILTERS( T[KPI] = "inside" )
	)
var __totalRowcount =
	COUNTROWS( T )
var __ratio =
	DIVIDE( __inside, __totalRowCount )
return
	__ratio

Please do not multiply the output by 100. Instead, format the number as Percentage.

Best

Darek

[simonrtaylor]

Thanks @Anonymous !