Forum Discussion

music583's avatar
music583
New Member
4 months ago
Solved

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

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 ...
Need Help
  • cengizhanarslan's avatar
    cengizhanarslan
    4 months ago

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

     

cengizhanarslan's avatar
cengizhanarslan
Icon for Super User rankSuper User
4 months ago

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's avatar
music583
New Member
4 months ago

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!