Forum Discussion

shiv1002's avatar
shiv1002
Regular Visitor
8 years ago
Solved

Calculate percentage based on selection and column value

I need to show the percentage of employees certified as well as not certified based on office selected from a drop-down office selection. I need to show a separate row for each selected office. Below...
  • SivaMani's avatar
    SivaMani
    8 years ago

    shiv1002,

     

    Create two calculated measure using DAX,

     

    Certified = DIVIDE(COUNTX(FILTER(Table1,Table1[Certified ] = "Y"),Table1[Emp Name]),COUNT(Table1[Emp Name]))

     

    Non - Certified = DIVIDE(COUNTX(FILTER(Table1,Table1[Certified ] = "N"),Table1[Emp Name]),COUNT(Table1[Emp Name]))

     

    Hope it will help you:)

Anonymous's avatar
Anonymous
Not applicable
8 years ago

HI shiv1002,

 

You can try to use below formula to create summary table with percentage of certified state.

Summary =
VAR _temp =
    SUMMARIZE (
        'Sample',
        [Office Name],
        "Y", CALCULATE ( COUNT ( 'Sample'[Certified] ), 'Sample'[Certified] = "Y" ),
        "N", CALCULATE ( COUNT ( 'Sample'[Certified] ), 'Sample'[Certified] = "N" )
    )
RETURN
    SELECTCOLUMNS (
        _temp,
        "Office Name", [Office Name],
        "Certified", [Y]
            / ( [Y] + [N] ),
        "Non-Certified", [N]
            / ( [Y] + [N] )
    )

 

Regards,

Xiaoxin Sheng

shiv1002's avatar
shiv1002
Regular Visitor
8 years ago

Hi Anonymous,

 

Creating a new table is a good idea, however, this would not work in my solution as I need to show only those offices which are selected from the list of offices. I will keep this in mind as this can be useful in other reports.

 

Regards,

Shiv Yadav