Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Ratio between two tables

 

Hi

 

This is my first post and question, hope someone helps me to resolve my query.

I have two different tables, Table1 (Employee Separations) and Table2 (Headcount). I have imported both tables in Power BI desktop.

 

In excel I can simply divide the separations with headcount to find the Attrition rate, but here in power BI. I am unable to calculate these things. I want the following result mentioned in last highlighted column.

 

 

 

 

 

Need Help
  • Vvelarde's avatar
    Vvelarde
    9 years ago

    Anonymous

     

    You can create a Measure:

     

    Attrition = Sum(Separations[Separations])/SUM(HeadCount[HeadCount])

    And use it in a table visual 

7 Replies

  • tringuyenminh92's avatar
    tringuyenminh92
    Memorable Member
    9 years ago

    Hi Anonymous,

     

    In case you had 2 tables (Headcount(Month,Headcount) & Separation(Month,Separation)):

     

    • In Modeling, choose new table by expression:

     

    Data = SUMMARIZE( NATURALINNERJOIN(Headcount,Separations),Headcount[Month],Headcount[Headcount],Separations[Separations])

     

    • Create calculated column for attrition ( this could be achieved by calculated measure as well )

     

    Attrition (%) = DIVIDE(Data[Separations],Data[Headcount])

     

     

     

     

    If this works for you please accept it as solution and also like to give KUDOS.

    Best regards
    Tri Nguyen

    • Vvelarde's avatar
      Vvelarde
      Community Champion
      9 years ago

      Anonymous

       

      You can create a Measure:

       

      Attrition = Sum(Separations[Separations])/SUM(HeadCount[HeadCount])

      And use it in a table visual 

      • parry2k's avatar
        parry2k
        Super User
        9 years ago

        Also I would like to add on top of what Vvelarde mentioned that there is relationship between botht those tables on a common field.