Find percentage of employee names according to state

[Anonymous]

Hello so imagine a table where i have 5 names : james, katie,jane,ari,jack. And each name is from a state on another table

total employees are 100 and like 20 james, 10 katie , 30 jane , 5 ari, 35 jack

i want to calculate the ratio of each employee name so like i have total james from california are 5 and total james are 20 so  5/20 *100 is 25% .... ect

NB: NAMES ARE IN A TABLE SO I HAVE 1 column its name : 

Name: state:

James california

James dakota

James california

James new jersey

.....

i appreciate the help

[Anonymous · Accepted solution]

Hi @Anonymous ,

You can create a measure as below to get it, please find the details in the attachment.

Ratio of each employee name = 
VAR _selstate =
    SELECTEDVALUE ( 'Table'[State] )
VAR _selname =
    SELECTEDVALUE ( 'Table'[Name] )
VAR _nscount =
    CALCULATE (
        COUNT ( 'Table'[Name] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Name] = _selname
                && 'Table'[State] = _selstate
        )
    )
VAR _ncount =
    CALCULATE (
        COUNT ( 'Table'[Name] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Name] = _selname )
    )
RETURN
    DIVIDE ( _nscount, _ncount, 0 )

Best Regards

[technolog]

Hi!

You can try create calculated table:

Calc table = SUMMARIZE( 'Table 1', 'Table 1'[Name], "NameCount", COUNT('Table 1'[ID]) )

And then add calc column to this table:

Calc column = DIVIDE ( [NameCount] , SUM( [NameCount] )

Please send me screenshots if it doesn't work!