Forum Discussion

simonrtaylor's avatar
simonrtaylor
Frequent Visitor
7 years ago
Solved

Return % based on COUNTIF

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's avatar
    Anonymous
    7 years ago
    -- 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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago
    -- 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