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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.