How to create a calculated column

[dreaves1]

I have a openings table and a hires table and they both have a unquie identifier and unique company in them. I'm looking to create a calculated column titled "Opening remaining" in the openings table that will subtract the number of openings based on the number of hires in the hire tables. How can I get this accomplished?

[v-yingjl · Accepted solution]

Hi @dreaves1 ,

You can create this calculated column in your opening table:

Opening remaining = 
VAR _count =
    CALCULATE (
        COUNT ( 'hires'[Hires] ),
        'hires'[Company Name] = EARLIER ( openings[Company Name] ),
        'hires'[SubCompany Name] = EARLIER ( openings[SubCompany Name] )
    )
RETURN
    'openings'[Total Openings] - _count

Attached a sample file in the below, hopes to help you.

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@dreaves1 ,Not very clear

Can you share sample data and sample output in table format?

Typically you can add subtract measure from two tables across common tables or grand total

Sum(Tables1[Value]) -Sum(Tables2[Value])

[dreaves1]

Thank you so much for your help! I've included some sample data on how my data looks in the post

[Greg_Deckler]

@dreaves1 Maybe:

Opening remaining =
  [Openings] - COUNTROWS(RELATEDTABLE('Hires')) 
  

Assuming a relationship, if not:

Opening remaining = 
  [Openings] - COUNTROWS(FILTER('Hires','Hires'[Company] = 'Openings'[Company]))

Filter criteria will vary depending on your data, I am not sure what you have exactly.

[dreaves1]

Thank you so much for your help!

I've included some sample data on what my tables look like.