Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
simonrtaylor
Frequent Visitor

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!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

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

Thanks @Anonymous !

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors