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