Forum Discussion

jeralee2's avatar
jeralee2
Frequent Visitor
4 years ago

Calculating Percentage Between 2 tables

Hello,

 

I feel like I should know how to do this or find this, but I'm drawing a blank. 

 

I have 1 table (Training Log) with records for each employee who has attended a training. It is sorted by State and Division.

 

I have another table (Total Employees by State) that contains the total number of employees in that state (I created this from a master employee table:

 

There is a one:many relationship from the Total Employees by State:Trainng Log created.

 

I need to develop a measure that counts the distinct number of rows in each state from the Training Log and compares that to the Total numer of employees and provides me with that percentage.  For example, if Nevada had 18 attendees, their attendance rate will be 50%.  This percentage will need to be able to change based on a Course Title slicer or a Date slicer.

 

Hopeful you have a suggestion.  Thank you!

 

 

Need Help

15 Replies

  • PabloDeheza's avatar
    PabloDeheza
    Solution Sage
    4 years ago

    Hi there!

    Try the following measure:

    VAR _TrainingLogEmployeed =

    DISTINCTCOUNT( 'Training Log' )

    VAR _TotalCount =

    SUM( 'Total Employees'[# of Employees] )

    VAR _Result =

    DIVIDE( _TrainingLogEmployeed, _TotalCount )

    RETURN

    _Result

     

    Let me know if it is helpful.

     

    • jeralee2's avatar
      jeralee2
      Frequent Visitor
      4 years ago

      Hi!  Thanks so much for responding! I think this got me closer!   But it doesn't seem to be respecting any slicer values.  It's giving me the percent of entire total instead of the percent of total by state.  I changed my data structure a bit and took out the grouped table and tried your method by joining just the employee table to the employees trained table:

       

      EmployeesTrainedPercent = VAR _EmployeesTrained =
      DISTINCTCOUNT(MasterAttendanceTable[Email])
      var _TotalCount =
      DISTINCTCOUNT('Allocations File'[Associate ID])
      var _result =
      DIVIDE(_EmployeesTrained,_TotalCount)
      return
      _result
       
      Do I need some kind of summarization or filter command?  My slicers are coming from the same table:
       

       

      • PaulDBrown's avatar
        PaulDBrown
        Community Champion
        4 years ago

        Have you created the calendar table?

        the measures posted in the other thread are tested and work. A simple division does not work at the total level because you need the sum of employees * the number of days. If you have locations with no activity on a particular day it will also affect the result. 
        If you read the other thread carefully you will see there are 3 different % calculations (all correct) computing different things. So pick the one you need and replicate the measure with your tables