Forum Discussion

dreaves1's avatar
dreaves1
Helper II
5 years ago
Solved

How to create a calculated column

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?

 

 

 

General Comment
Need Help
Show and Tell
Tips and Tricks
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    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.

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    5 years ago

    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's avatar
      dreaves1
      Helper II
      5 years ago

      Thank you so much for your help!

       

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

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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's avatar
      dreaves1
      Helper II
      5 years ago

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

  • v-yingjl's avatar
    v-yingjl
    Community Support
    5 years ago

    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.