Help with sum subtract formula based on other columns

[rjspereira]

Hi,

So i have a list of processes and for each one, several activities with nr of days associated. (Below is an example)

Now i need to calculate a new nr of days column with some rule based o ActivityID.

[A]ProcID	[B]ActivityID	[C]ActivityName	[D]Nr of Days	NewColumn
P001	1	Start	5	=D2 = 5
P001	2	Validation	14	=D3 = 14
P001	3	Analysis	20	=D4-D5-D6 = 9
P001	4	Inputs1	5	=D5 = 5
P001	5	Imputs2	6	=D6 = 6
P001	6	Preparation	8	=D7 = 8
P001	7	Postponement1	3	=D8 = 3
P001	8	Postponement2	2	=D9 = 2
P001	9	Query	10	=D10-D8-D9 = 5
P001	10	End	19	=D11 = 19
P002	1	Start	2	=D12 = 2
P002	2	Validation	12	=D13 = 12
P002	3	Analysis	33	=D14-D15-D16 = 26
P002	4	Inputs1	3	=D15 = 3
P002	5	Imputs2	4	=D16 = 4
P002	6	Preparation	21	=D17 = 21
P002	7	Postponement1	1	=D18 = 1
P002	8	Postponement2	9	=D19 = 9
P002	9	Query	40	=D20-D18-D19 = 30
P002	10	End	13	=D21 = 13

The rule is this:

If ActivityID = 3, Then [NewColumn] = (Nr of Days for ActivityID=3) - (Nr of Days for ActivityID=4) - (Nr of Days for ActivityID=5)

If ActivityID = 9, Then [NewColumn] = (Nr of Days for ActivityID=9) - (Nr of Days for ActivityID=7) - (Nr of Days for ActivityID=8)

All other ActivityID, [NewColumn] = [Nr of Days]

How can i achieve this calculations? using a calculated column or using a measure. Any sugestion?

Thanks

[v-janeyg-msft · Accepted solution]

Hi, @rjspereira 

I created a column using your sample. It is recommended to use column, which can be used on other visuals without restriction.

Like this:

Column = 
var D3= CALCULATE(MAX('Table'[Nr of Days]),FILTER('Table',[ActivityID]=3&&[ProcID]=EARLIER([ProcID])))
var D4= CALCULATE(MAX('Table'[Nr of Days]),FILTER('Table',[ActivityID]=4&&[ProcID]=EARLIER([ProcID])))
var D5= CALCULATE(MAX('Table'[Nr of Days]),FILTER('Table',[ActivityID]=5&&[ProcID]=EARLIER([ProcID])))
var D7= CALCULATE(MAX('Table'[Nr of Days]),FILTER('Table',[ActivityID]=7&&[ProcID]=EARLIER([ProcID])))
var D8= CALCULATE(MAX('Table'[Nr of Days]),FILTER('Table',[ActivityID]=8&&[ProcID]=EARLIER([ProcID])))
var D9= CALCULATE(MAX('Table'[Nr of Days]),FILTER('Table',[ActivityID]=9&&[ProcID]=EARLIER([ProcID])))
RETURN SWITCH(TRUE(),[ActivityID]=3,D3-D4-D5,[ActivityID]=9,D9-D8-D7,[Nr of Days])

Did I answer your question? Please mark my reply as solution. Thank you very much.

If not, please feel free to ask me.

Best Regards,

Community Support Team _ Janey

[filipeoliveira]

Try with

MeasureName = 
VAR ActivityID_3 = CALCULATE(SUM('TableName'[Nr of Days]),SELECTEDVALUE('TableName'[ActivityID])=3)
VAR ActivityID_4 = CALCULATE(SUM('TableName'[Nr of Days]),SELECTEDVALUE('TableName'[ActivityID])=4)
VAR ActivityID_5 = CALCULATE(SUM('TableName'[Nr of Days]),SELECTEDVALUE('TableName'[ActivityID])=5)
VAR ActivityID_7 = CALCULATE(SUM('TableName'[Nr of Days]),SELECTEDVALUE('TableName'[ActivityID])=7)
VAR ActivityID_8 = CALCULATE(SUM('TableName'[Nr of Days]),SELECTEDVALUE('TableName'[ActivityID])=8)
VAR ActivityID_9 = CALCULATE(SUM('TableName'[Nr of Days]),SELECTEDVALUE('TableName'[ActivityID])=9)
RETURN
SWITCH(TRUE(),
SELECTEDVALUE('TableName'[ActivityID])=3, ActivityID_3 - ActivityID_4 - ActivityID_5,
SELECTEDVALUE('TableName'[ActivityID])=9, ActivityID_9 - ActivityID_7 - ActivityID_8,
SUM('TableName'[Nr of Days])
) 

 If it does not work for you, try a calculated column (same formula just remove SELECTEDVALUE)

[rjspereira]

Tried the measure, but keep receiving this error:

I have tried a calculated column, but without sucess. 

As it seems that the expression is evaluated row by row, and will not sum values from another rows (activityIDs).

Here is a link for a pbix example:

PBI_example.pbix