cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
manutejedor
Frequent Visitor

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_prod" there are different "Projects" that can have several "Tasks", and each "Task" has a "Duration". The table "his_prod_op" defines the "Persons" that have participated in each "Task". 

 

Sample.jpg

What I intend to do is to summarize the total "Duration" of the "Projects" in which a specific "Person" has participated, no matter the "Tasks" he's been in, this is:

Sample 2.jpg

 

The problem I am facing is that the moment I filter by "Person", no matter what DAX formula I use (SUM, SUMX, CALCUALTE, etc), all I get is the "Duration" of the "Task" in which that person has participated, this is (in red wrong values):

 

Sample 3.jpg

 

Is there any formula to calculate this "Duration" by "Job"? Thanks for the help!

 

Manu

1 ACCEPTED SOLUTION
v-yulgu-msft
Microsoft
Microsoft

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] ) )
)

1.PNG

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-yulgu-msft
Microsoft
Microsoft

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] ) )
)

1.PNG

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

LivioLanzo
Solution Sage
Solution Sage

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

 

Capture.PNG

 

 

then you can add this measure:

 

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

Capture.PNG

 

 

You can download the file here:

 

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

 

 

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.