Forum Discussion

FrisoW's avatar
FrisoW
Helper I
6 years ago
Solved

Calculated column with values from 2 tables

Hi,

For this issue I narrowed my project down to just 3 tables: 1 Excel, 1 SQL server table and 1 calendar table.
There is no relationship other than the date column (circled in red).

I'd like to devide the value of column "TotalHours" in table 1 with column "Overtime" in table 2 (to show the normal hours / overtime ratio).
When creating a new calculated column to do so, I can't (I suppose because there's no direct relationship between the 2 tables?).

How can I approach such issue?


Thanks

 

Need Help
  • Mariusz's avatar
    Mariusz
    6 years ago

    Hi FrisoW 

     

    You can create Measures like 

     

    TotalHrs= SUM( DataTable[TotalHours] )
    OvertimeHrs = SUM( Table[Overtime] )

     

    % of Overtime = DIVIDE( [TotalHrs], [OvertimeHrs] )
    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

     



4 Replies

  • Mariusz's avatar
    Mariusz
    Community Champion
    6 years ago

    Hi FrisoW 

     

    You can create Measures like 

     

    TotalHrs= SUM( DataTable[TotalHours] )
    OvertimeHrs = SUM( Table[Overtime] )

     

    % of Overtime = DIVIDE( [TotalHrs], [OvertimeHrs] )
    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

     



    • FrisoW's avatar
      FrisoW
      Helper I
      6 years ago

      Using this method I seem to be able to calculate values from different tables (I don't get why following formula doesn't work, but anyway, glad it works this way)

      % of Overtime = SUM(Table[Overtime]) / SUM(DataTable[TotalHours])

       

       

      Problem now though is that it doesn't calculate each row (although the measures have values, as seen in the table):

      And the calculation isn't correct, row 2 for example should be 173 / 6664,19 = 0,0259596 or 2,59% (not 38431,57)

       

       

      Thanks!

      • FrisoW's avatar
        FrisoW
        Helper I
        6 years ago

        Solved with a measure calculating OvertimeHRS / TotalHRS, I had used a calculated column which returned the strange values.

         

        Still don't know why it works this way, but I'm glad it works, thanks.