Calculate percentage based on selection and column value

[shiv1002]

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

[Ashish_Mathur]

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.

[v-shex-msft]

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]

Hi @v-shex-msft,

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

[SivaMani · Accepted solution]

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