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

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 

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.

     

     

     

5 Replies

  • 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.

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    5 years ago

    Hi, Anonymous 

    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

     

     

     

     

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    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.