cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tonyclifton
Helper II
Helper II

Aggregate Sum based on Column

Hello community,

 

I am doing my first steps with Power BI and now I've gotten to a point where I require some help.

In below example I have row values that need to be aggregated based on the ParentID

 

If the sum of Current for all rows of that ParentID is greater than ZERO, then put this value in another measure/column which can be used in other calcuations.

At the moment I use a "Combined" column which gives wrong sums because I can only check each row if a Current value exists.

The correct sum for those two examples is 3350 and 10.

 

 

x.PNG

 

Let me know if you need more details.

 

Hope you can help me.

 

Best regards,

Thorsten

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

Hi @tonyclifton

measure below

Combined = IF(SUM(Sheet5[Current])>0,SUM([Current]),0)

17.png

Best Regards

Maggie

View solution in original post

3 REPLIES 3
v-juanli-msft
Community Support
Community Support

Hi @tonyclifton

measure below

Combined = IF(SUM(Sheet5[Current])>0,SUM([Current]),0)

17.png

Best Regards

Maggie

vanessafvg
Super User
Super User

@tonyclifton

 

might be an easier way but what i would first do is summarize the table

 

ie. summary = SUMMARIZE(Table2, Table2[ParentID], "TotalCurrent", sum(Table2[Current]))

 

then i would create a relationship between the 2 tables on parentid

 

then i would create a measure in the first table

test = if(sum(summary[TotalCurrent]) > 0, sum(summary[TotalCurrent]), 0)





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




@vanessafvgthank you. This seems to do the trick to get the correct sums. However I missed to add one important column to the example which is needed to present the values by a status.

Each ChildID can have a different Status

 

If the sum of Current for a ParentID is greater than zero, set the value of the "Combined" column to zero where a Budget value exists in order to also get the correct values for each row.


Example:

y.PNG

 

Is that possible?


Thanks.

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors