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 ...
Need Help
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

      • jeralee2's avatar
        jeralee2
        Frequent Visitor
        4 years ago

        Hi!  I did read through but I noticed a CALCULATE...ALL which from another video I watched, apparently doesn't work anymore with newer versions of PowerBI Desktop.  Maybe you can confirm that for me? 

         

        I also don't need to know the percentage as of a point in time.  I only need the number of employees as of today, so a calendar table isn't necessary.  I realize this might throw my calculation off slightly, but we aren't concerned with exacts as we have very low turnover. We just want to know how many trainees actually attended the class vs how many COULD have attended.