Forum Discussion

manutejedor's avatar
manutejedor
Frequent Visitor
7 years ago
Solved

SUM values by higher aggregation

Hi! I have a very simple sample of data with two tables: "his_prod" and "his_prod_op". "His_prod" is linked to "his_prod_op" by a "one to many" relationship on the field "Task". In the table "his_pro...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    Hi manutejedor,

     

    LivioLanzo's suggestion works great. But you can create a simpler one, without adding an extra Person table.

    Total duration =
IF (
    COUNTROWS ( his_prod_op ),
    CALCULATE ( SUM ( his_prod[Duration] ), ALLSELECTED ( his_prod_op[Person] ) )
)

    Best regards,

    Yuliana Gu

LivioLanzo's avatar
LivioLanzo
Solution Sage
7 years ago

Hello manutejedor,

 

you can reshape your model this way where you allocated a task time to each person proportially, should be farily easy within Power query with a couple of joins

 

 

 

then you can add this measure:

 

=IF( 
    COUNTROWS( his_prod_op ),
    CALCULATE( 
        SUM( his_Prod_op[Durarion Allocation] ), 
      ALL( Persons[Person] )
    )
)

 

 

You can download the file here:

 

https://1drv.ms/x/s!AiiWkkwHZChHj1eypR2cbxq8ho32