Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Summing values from second table to another

Hi,

I try to create a measure, which sums up working hours in Quarters. There is a connection between two tables based on Month name & Year. Based on table 2 i want to show sum of working hours based on quarters from table1. 

 

Table1

 

Table2:

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi  Anonymous ,

    Here are the steps you can follow：

    1. Create measure.

    Measure =
var _right=VALUE( RIGHT(MAX('Table2'[monthname]),4))
return
CALCULATE(SUM('Table1'[Working Hours]),FILTER(ALL(Table1),
'Table1'[Year]=_right&&'Table1'[Quarters]=MAX('Table2'[Quarter])))

    2. Result:

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

4 Replies

  • NickolajJessen's avatar
    NickolajJessen
    Solution Sage
    4 years ago

    What do you mean "Based on  table2"
    What information is in table2, that is not already in table1?

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    I want to show sum of working hours per quarter from table1 in working hours column.

     

    Table2:

     

  • NickolajJessen's avatar
    NickolajJessen
    Solution Sage
    4 years ago

    For that you can just drag Quarter field and hours field onto a visualization, no?

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi  Anonymous ,

    Here are the steps you can follow：

    1. Create measure.

    Measure =
var _right=VALUE( RIGHT(MAX('Table2'[monthname]),4))
return
CALCULATE(SUM('Table1'[Working Hours]),FILTER(ALL(Table1),
'Table1'[Year]=_right&&'Table1'[Quarters]=MAX('Table2'[Quarter])))

    2. Result:

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly