Sum formula with condition by level

[kbauro97]

Hi, 

I've been trying to make this work but I haven't found the answer.

I have 3 levels: Zone, Component and person. For example, this is the data:

Zone	Component	Person	Work hrs
Zone 1	Component 1	Simon	1.5
Zone 1	Component 1	Peter	-1
Zone 1	Component 1	Simon	2
Zone 1	Component 2	Simon	4
Zone 1	Component 2	Peter	-2
Zone 1	Component 2	Simon	-7
Zone 1	Component 2	Simon	-4
Zone 1	Component 2	Peter	1

I want to create a formula where I would sum only the negative values from the person's sum to obtain the component's sum. The positives values will turn 0.

I would like to create a table where I can get the column "Data": 

Here you can see the column "Normal sum", It sums positives and negatives to obtain the component sum.

I want to obtain the data column, where the positive values from the person will turn to 0 (such as the 3.5 from Simon), so that I can only sum the negative hrs and obtain the -9 value.

Which formula could I use?
I hope you can help me to solve this issue. Thank you.

[v-jayw-msft · Accepted solution]

Hi @kbauro97 ,

Check the formulas.

measure = CALCULATE(SUM(TableA[Work hrs]),ALLEXCEPT(TableA,TableA[Zone],TableA[Component],TableA[Person]))

Measure 2 = IF([measure]>0,0,[measure])

Measure 3 = SUMX(TableA,[Measure 2]/CALCULATE(COUNT(TableA[Person]),ALLEXCEPT(TableA,TableA[Zone],TableA[Component],TableA[Person])))

Result:

Best Regards,

Jay

[amitchandak]

@kbauro97 ,

Create a measure first

normal sum = sum(Table[Value])

Then

sumx(filter(values(Table[Person]), [Normal sum] >0 ), [Normal Sum])