Forum Discussion

PatrickLamoste's avatar
PatrickLamoste
Icon for Helper I rankHelper I
6 years ago
Solved

Sum with ID and Date from a different table

Hi,   I am currently working on 2 tables. The 1st table has the regular hours and the 2nd one has the OT hours.   What I am trying to achieve is to have the OT hours in  a new column in the Regul...
Need Help
Show and Tell
Tips and Tricks
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    hi, PatrickLamoste 

    For your case, you need to create a new table by this formula:

    Table = 
SUMMARIZE (
    Table1,
    Table1[Employee ID],
    Table1[Date],
    "Regular Hours", CALCULATE ( SUM ( Table1[Hours] ) ),
    "OT Hours", CALCULATE (
        SUM ( Table2[Hours] ),
        FILTER (
            Table2,
            Table2[Employee ID] = Table1[Employee ID]
                && Table2[Date] = Table1[Date]
        )
    )
)

    Result:

     

    and here is sample pbix file, please try it.

     

    Regards,

    Lin

PatrickLamoste's avatar
PatrickLamoste
Icon for Helper I rankHelper I
6 years ago

Hi v-lili6-msft  Lin,

 

Thank you for your reply. I tried to tweak the formula a bit but still a dead end.

 

I was wondering if it's possible to summarize Table2 [OT] and will be put into Table1 [RegularHours]. But since Table 1 has duplicate dates I need to only display 7 and not the sum of the whole row when  I put it in a visual. Like this.

 

Table1    
Employee IDHour TypeDateHoursOT Hours Sum
110290Regular Hours24/04/201917
110290Regular Hours24/04/201937
110290Regular Hours24/04/201927

 

Table2   
Employee IDHour TypeDateHours
110290OT Hours24/04/20192
110290OT Hours24/04/20193
110290OT Hours24/04/20192

 

So when I put in a table visual it would look like this:

 

Visual: Table   
Employee IDDateRegular HoursOT Hours
11029024/04/201967

 

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
6 years ago

Hi,

If your end objective is to show Regular and OT hours in a single Table visual, then you need not bring over OT hours from Table2 into Table1.  If my approach looks suitable to you, then post back and I will share my solution with you.