Percentage of user attendance between 2 tables

[Kadd]

Hi all,

hope you're doing good.

i'm not too experienced with Power BI / Power Query.

I have  2 tables.

Table 1 is about department names and number of people attendance (to a training)

Table 2 is about the same department and the total number of people in each department

Table 1                                                               Table 2

Dept             Attendance         SessionID                                   Dept            TotalUsers

---------------------------------------------                                --------------------------------

SAM                13                       Session1                                   SAM                 40

BOB                 28                      Session1                                    BOB                  39

XEC                  3                       Session1                                     XEC                 48

MBF                 8                        Session1                                    MFB                 62

SAM                12                       Session2

XEC                  4                        Session2

MFB                 41                      Session2

What i'm trying to find is the total percentage of users from the departments who attended the training

so.... SAM is 13 + 12 = 25  there are 40 people in SAM. it means the number of attendance is (25/40) * 100 = 62,5%

and at the end i can add this to a matrix visual showing  (this visual i have it and used measures to sum numbers up...but i'm missing the percentage column)

Dept         Attendance    Percentage

-------------------------------------------

SAM             25                     62,5

BOB              28                     71,8

XEC               7                       14,5

....

Clearly there's something i didn't manage to catch cause i can't do it with a measure and can't find a way to do it in Power Query either...sorry 😞

Would someone be able to help me please ?

Thanks ! 🙂

[Kadd · Accepted solution]

ok so....

I gave it another try ..

The offered solution was about creating new queries in Power Query so everything would be processed in the background and i could simply add the columns to my visual. But it didn't work for me

.... i found another way.... I did it in Power BI "studio" using measures.  I got stuck a little bit wondering how i could get a values of my "TotalUsers" into my calculation but overall it was clearer for me.

So in my Table 1, i created a measure to sum up all the occurences of attendance for the departments :

NbrAttendance = SUM('Table 1'[Attendance])

In my Table 2, i created another measure that will calculate the percentage based on the sum of attendance and the TotalUsers for each department

PercentAttendance = 
   var v1=[NbrAttendance]
   var v2=MAXX(FILTER('Table 2','Table 2'[Dept] IN SELECTCOLUMNS('Table 1',"Dept",'Table 1'[Dept])),'Table 2'[TotalUsers])
   RETURN
   DIVIDE(v1,v2)*100

This will take the number of users from the departments (v1) and compare the names of the departments in Table 1 and 2 to extract the values of "TotalUsers" for each department (v2) and finaly calculate the percentage values (DIVIDE function).

Then when i add it to my visual (matrix) with :

* Rows :  Dept (from Table 1)

* Values : NbrAttendance (from Table 1)

                PercentAttendance (from Table 2)

I get what i need :

Dept      Attendance      Percentage

-------------------------------------------

SAM      25                      62,5

BOB      28                      71,8

XEC       7                       14,5

....

et voila ! 🙂

[lbendlin]

so.... SAM is 13 + 12 = 25  there are 40 people in SAM. it means the number of attendance is (25/40) * 100 = 62,5%

That's not what that means.

It means that out of 40 people 13 attended Session 1 and 12 attended Session 2.  These can be the same people.

You need more granular source data, including the employee ID.

[Kadd]

Hi @lbendlin ,

Yes you're right. I first told the same to my client. but the answer was "no, everyone has to come. we have a side count on who comes several times"

so data is like this and "correct"

even if someone comes 2 times, he/she will be counted as 1 attendance overall.

so my statement is correct but i don't understand how to do this.

Thanks

[lbendlin]

Table 1:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCnb0VdJRMjQGEsGpxcWZ+XmGSrE60UpO/k5AISMLdPEIV2egEIZyXyc3oBCGaqjpRghxIyRTTNCFIaaYGKKIxwIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Dept = _t, Attendance = _t, SessionID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Attendance", Int64.Type}})
in
    #"Changed Type"

Table 2:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCnb0VdJRMjFQitWJVnLydwJyjC3BnAhXZ5CMBZjj6waSMTNSio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Dept = _t, TotalUsers = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TotalUsers", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Attendance", (k)=> List.Sum(Table.SelectRows(#"Table 1", each [Dept]=k[Dept])[Attendance]), Int64.Type)
in
    #"Added Custom"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

[Kadd]

Hi @lbendlin 

waw...thanks !

I'm trying to do what you say. I copied the code in new queries but it won't work and readapted the sources and...the name of the columns to match the original ones.

hmm...i spent the day on this trying different things i might be a bit tired 😅

I'll give it another try tomorrow.

Thanks !! 😊