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 is the sample of data and desired output:

   



Thanks in advance

  • 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:)

4 Replies

  • SivaMani's avatar
    SivaMani
    Icon for Resident Rockstar rankResident Rockstar
    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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    8 years ago

    Hi,

     

    Drag Office to the visual and write the following measures:

     

    Certified Employees = CALCULATE(COUNTROWS(Data),Data[Certified]="Y")/COUNTROWS(Data)

    Non-Certified Employees = CALCULATE(COUNTROWS(Data),Data[Certified]="N")/COUNTROWS(Data)

     

    Hope this helps.