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
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors