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:

EmployeeDeadline 1Deadline 2Deadline 3
Person 1YesYesYes
Person 2Yes

No

No
Person 1YesNoNo
Person 3YesYesYes
Person 3YesNoYes

 

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

EmployeeDeadline 1Deadline 2Deadline 3
Person 1100%50%50%
Person 2100%0%0%
Person 3100%50%100%
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] ) ) )
)

     

6 Replies

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

      • cengizhanarslan's avatar
        cengizhanarslan
        Icon for Super User rankSuper User
        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] ) ) )
)

         

  • Deku's avatar
    Deku
    Icon for Super User rankSuper User
    4 months ago

    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

     

  • krishnakanth240's avatar
    krishnakanth240
    Icon for Super User rankSuper User
    4 months ago

    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%