Forum Discussion
Sum formula with condition by level
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.
Hi Anonymous ,
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
2 Replies
- amitchandak
Super User
Anonymous ,
Create a measure first
normal sum = sum(Table[Value])
Then
sumx(filter(values(Table[Person]), [Normal sum] >0 ), [Normal Sum])
Hi Anonymous ,
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
