Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to 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.
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.
@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])
Thank you so much for your help! I've included some sample data on how my data looks in the post
@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.
Thank you so much for your help!
I've included some sample data on what my tables look like.
User | Count |
---|---|
101 | |
90 | |
79 | |
70 | |
70 |
User | Count |
---|---|
112 | |
96 | |
96 | |
75 | |
72 |