Forum Discussion

rjspereira's avatar
rjspereira
Frequent Visitor
4 years ago
Solved

Help with sum subtract formula based on other columns

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 Ac...
Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    4 years ago

    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's avatar
filipeoliveira
Icon for Helper I rankHelper I
4 years ago

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's avatar
    rjspereira
    Frequent Visitor
    4 years ago

    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