Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
trina46844
Regular Visitor

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

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.

 

Test.JPG

2 ACCEPTED SOLUTIONS
jdbuchanan71
Super User
Super User

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

View solution in original post

Ashish_Mathur
Super User
Super User

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

3 REPLIES 3
Ashish_Mathur
Super User
Super User

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
jdbuchanan71
Super User
Super User

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

Thanks for your help.  This worked.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.