Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Summary table

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 h...
Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    5 years ago

    Hi, Anonymous 

    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.

     

     

     

westwrightj's avatar
westwrightj
Resolver III
5 years ago

Hey Anonymous 

 

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.

 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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.