Forum Discussion
Calculating Percentage Between 2 tables
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
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.
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.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
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?