Calculating Percentage Between 2 tables

[jeralee2]

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!

[PaulDBrown]

See if this thread helps. It's pretty much the same question

https://community.powerbi.com/t5/Desktop/Calculating-percent-using-data-from-two-tables/td-p/2427263 

[PabloDeheza]

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]

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]

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]

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.  

[PaulDBrown]

As regards the division, you have to take into account the number of locations in which there have been courses on a particular day. Say Today there have been courses in 5 out of 10 locations, but yesterday there were couses in 10 out of 10 locations. 
In the thread, the first calculation is over the 10 locations always. The second calculation only computes attendance on the day. So today is the 5 locations is 100%. The third calculation is the average attendance. So the totals are the average  of individual % (so the totals are not weighted by the number of people). 
make sense?

[jeralee2]

Hi there!  I appreciate the dialogue.  The courses are given online, so they are not attached to a particular location.  The division is attached to the employee.  So Alabama has X number of employees that could have attended, but they had Y number of employees that actually attended.   So, for instance the Safety class, we have 40 alabama employees that could have gone to training, but only 10 did.  So my percentage for alabama was 25%.   Conversely, we have 50 ohio employees that could have gone to the training, but only 25 did, so that's 50%.  Maybe I'm looking at it a bit too simply?  

[PaulDBrown]

If you're still stuck please share dummy data for the tables to work on the solution 

[jeralee2]

Hi..Thank you.  I still don't think we are on the same page so I stepped away to think about this a bit more.  

I don't care where the course was held (because it was online) or what day it was held).  In aggregate, I need a total number of employees who attended classes vs total number of employees. Period.   The division is a piece of metadata attached to the employee and it's the same across both tables.  So if I have 2 attendees from Ohio, 2 from Alabama and 2 from Nevada for a total of 6 attendees, then I have 10 total employees from Ohio, 14 from Alabama, and 6 from Nevada, my percentages would be 20% Ohio (2/10), 14% Alabama (2/14), 33.3% Nevada (2/6).  If I'm not making sense, should I just start over?

Thank you!

[PaulDBrown]

Either I'm being really obtuse or I'm completely misunderstanding your scenario. You say location is irrelevant, but then say "I have 2 attendees from Ohio, 2 from Alabama and 2 from Nevada for a total of 6 attendees,".

If you only have one course going on, go with the direct division between the DISTINCTCOUNT of the names attending the course over of the SUM of employees by location. 
To be able to help further, please provide sample data to work with.
And, regardless, please read this thread:

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/2066986#M7710... 

Thanks!

[jeralee2]

I was responding to your comment about locations:  "Say Today there have been courses in 5 out of 10 locations, but yesterday there were couses in 10 out of 10 locations."  When I say that the location is irrelevant, I mean we don't do courses in actual locations.  The location is a piece of metadata about the employee and it will be used as a slicer, just like gender could be used, or job title, or something like that.  I thought I provided sample data in the beginning of this thread.  I can do that again, but I am not allowed to use dropbox on my computer, so that has always proven difficult for me to attach anything to these forums. 

[PaulDBrown]

You intitally  posted images of your data, with which we cannot work unless we manually type in the values into Excel.

If you have sample, non-confidential data, you can copy and paste it into a post.

[PaulDBrown]

A simple division will get you the % per state. The problem is with the totals. Following your example, what if tomorrow there are only courses in Ohio? Since the number of students is in the students table, the DISTINCTCOUNT is over that table. So if you put it in a matrix, you will see a value for number of students for every state, regardless of whether  there was a course on the day. 
so the simple division will give you the number of students who attended over ALL the students registered in all the states (not over those who actually had a course on the day). So that is the first calculation in the other thread. 
If you want the % of attendance of those who had a course that day, you need to filter out States which weren't involved in a course on the day. 
which is the % you need?

[PaulDBrown]

The ALL function is still working. It's just that REMOVEFILTERS was introduced which is similar to ALL, but perhaps easier to understand out of the box. 
If you are calculating % based on TODAY, you won't need the number of days calculation. 

[PaulDBrown]

Btw, rechecking your employee table by state, you should be using SUM instead of DISTINCTCOUNT. 
As regards the attendance table, if the detail is by employee use DISTINCTCOUNT; if the detail is by number of student use SUM