Calculating Percentage A Given Value of One Column Has Values In Other Columns

[music583]

I need to get a table that looks like this:

Employee	Deadline 1	Deadline 2	Deadline 3
Person 1	Yes	Yes	Yes
Person 2	Yes	No	No
Person 1	Yes	No	No
Person 3	Yes	Yes	Yes
Person 3	Yes	No	Yes

To look like this (calculating the percentage of the time that each employee meets a given deadline):

Employee	Deadline 1	Deadline 2	Deadline 3
Person 1	100%	50%	50%
Person 2	100%	0%	0%
Person 3	100%	50%	100%

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[cengizhanarslan]

Please try the measures below:

Deadline 1 % =
DIVIDE (
    COUNTROWS ( FILTER ( KEEPFILTERS ( Table1 ), Table1[Deadline 1] = "Yes" ) ),
    COUNTROWS ( Table1 )
)

Deadline 2 % =
DIVIDE (
    COUNTROWS ( FILTER ( KEEPFILTERS ( Table1 ), Table1[Deadline 2] = "Yes" ) ),
    COUNTROWS ( Table1 )
)

Deadline 3 % =
DIVIDE (
    COUNTROWS ( FILTER ( KEEPFILTERS ( Table1 ), Table1[Deadline 3] = "Yes" ) ),
    COUNTROWS ( Table1 )
)

Place Employee in the Rows field of a Matrix visual and add all three measures as Values. Format each measure as percentage via Format visual → Values → display units.

[music583]

That worked like a charm. Unfortunately I forgot one factor. Sometimes the employee hasn't submitted a file yet, so the deadline met column is blank. Following your code, that blank row is included.  I know it needs a filter, but I can't quite figure out how (I work way more with Power Query than DAX). Thank you!

[cengizhanarslan · Accepted solution]

Just wrap the denominator in a FILTER to exclude blank rows: 

Deadline 1 % =
DIVIDE (
    COUNTROWS ( FILTER ( KEEPFILTERS ( Table1 ), Table1[Deadline 1] = "Yes" ) ),
    COUNTROWS ( FILTER ( KEEPFILTERS ( Table1 ), NOT ISBLANK ( Table1[Deadline 1] ) ) )
)

[krishnakanth240]

Hi @music583 

Deadline 1% =

DIVIDE(CALCULATE(COUNTROWS('Table'),'Table'[Deadline 1] = "Yes"),COUNTROWS('Table'))

Change it to Percentage by selecting the measure from formatting option.

Similarly you can write measures for Deadline 2% and Deadline 3%

[Deku]

Something like below, with a measure per deadline field. For a clean model and more generalized solution you might be better off depivoting the data first, so that you have 3 columns: employee, deadline index, value

Deadline1_perc=

Var total = countrows( tbl )

Var metDeadline =

Calculate(

   Countries( tbl ),

   Tbl[deadline 1] = "Yes"

)

Return

metDeadline/total