Summary table

[krixtsup3r]

Hi.

I am not sure how to name my problem correctly but here it is. 

I have a production table that shows each production of employee per prod id

then I also have an attendance table

how can I create a dynamic summary that can get an accurate attendance rate? wherein the computation is present hours / expected hours. I have already tried relate the table but it wouldn't be accurate as the date of absence is not available in  the production data. Also, relating it will make the total hours of attendance related data to be duplicated.

Sample output will be like:

Saying the below table might be filtered by Production ID: 122 and Date Coverage is from Nov 1 - 4 

[v-easonf-msft · Accepted solution]

Hi, @krixtsup3r 

Does column "ASP Id" (ID #1) also exist in Table "Roster"?

I guess that you directly establish a relationship between "ASP Id" in  Table"UniqueASP_IDs" and "ID #2" in Table "Roster.

You need to create another table column "ID #2" in  Table"UniqueASP_IDs".Then building  relationship between  the new column"ID #2" in  Table"UniqueASP_IDs" and "ID #2" in Table "Roster.

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-easonf-msft]

Hi, @krixtsup3r 

Is there any difference between ID #1 and ID #2 in terms of data?

Do you mean that each employee has two different types of IDs for identification?

Best Regards,
Community Support Team _ Eason

[krixtsup3r]

Hi @v-easonf-msft yes that is correct.

[westwrightj]

Hey @krixtsup3r 

So there are alot of ways to do what you are trying to accomplish.

I suppose the first thing I would suggest is building in an employee bridge table that is just a table with each unique employee id

From here we can start working on the measures you identified

Total Value = 
CALCULATE(SUM('Production Table'[Value]))

Would give us a table that looks like this.

Then we can do a calcuation for attendance

Attendance Rate = 

(CALCULATE(SUM('Attendance Table'[Present Hours]))
/
CALCULATE(SUM('Attendance Table'[Expected Hourse])))
+ 0

Which gives us this summary table

Though it looks like in your example you are treating absence hours against the employee so we can make a column that takes the present and subtracts absent and then make a second attendace rate calculation

Present Minus Absent =

'Attendance Table'[Present Hours] - 'Attendance Table'[Absence Hourse]

Attendance Rate Adjusted for Absence Hours= 

(CALCULATE(SUM('Attendance Table'[Present Minus Absent]))
/
CALCULATE(SUM('Attendance Table'[Expected Hourse])))
+ 0

Add that to our table and we get this output

Let me know if that solution works for you.

[krixtsup3r]

Thank you, @westwrightj for your reply. But I think i did get into another road block.

I have different IDs (or identifiers to connect each employee on different table) which makes it crazy.

Here are my tables:

• UniqueASP_IDs - unique table for ID #1
• Roster - unique table for ID #2 and has the full detail of employee
• Attendance Data - data that only have ID #1 to connect to
• Judgment Data - data that only have ID #2 to connect to

I cant seem to get it right.