Forum Discussion
Calculating Percentage Between 2 tables
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.
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?
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?
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?If you're still stuck please share dummy data for the tables to work on the solution
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!