Forum Discussion

tonyclifton's avatar
tonyclifton
Helper III
8 years ago
Solved

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 Pare...
  • v-juanli-msft's avatar
    v-juanli-msft
    8 years ago

    Hi tonyclifton

    measure below

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

    Best Regards

    Maggie

vanessafvg's avatar
vanessafvg
Community Champion
8 years ago

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)

tonyclifton's avatar
tonyclifton
Helper III
8 years ago

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:

 

Is that possible?


Thanks.