Calculate Percentage of Employed individuals on contract vs Authorized by Company from 2 Tables

[trina46844]

Requesting assistance calculating the number of indivduals who are actually employed vs the number who are authorized for employment for each company.  Table 1 is those actually employed.  Table 2 are positions that are authorized.  Results is what I'm looking to achieve.  Please help.  Thanks.  I appreciate your assistance.

[jdbuchanan71 · Accepted solution]

@trina46844 Do you have a company table that sits over 'Table 1' and 'Table 2' and joins them together?

If not you can create one by creating a new table with a DAX formula like this.

Companies =
DISTINCT (
    UNION (
        DISTINCT ( 'Table 1'[Fill Company] ),
        DISTINCT ( 'Table 2'[Fill Company] )
    )
)

You would join that into your data tables then your result would be something like 

RESULT =
DIVIDE ( COUNTROWS ( 'Table 1' ), SUM ( 'Table 2'[Auth Count] ) )

[Ashish_Mathur · Accepted solution]

Hi,

Share the source data from where those 2 tables have been built.

[jdbuchanan71 · Accepted solution]

@trina46844 Do you have a company table that sits over 'Table 1' and 'Table 2' and joins them together?

If not you can create one by creating a new table with a DAX formula like this.

Companies =
DISTINCT (
    UNION (
        DISTINCT ( 'Table 1'[Fill Company] ),
        DISTINCT ( 'Table 2'[Fill Company] )
    )
)

You would join that into your data tables then your result would be something like 

RESULT =
DIVIDE ( COUNTROWS ( 'Table 1' ), SUM ( 'Table 2'[Auth Count] ) )

[trina46844]

Thanks for your help.  This worked.