To create measure/column to calculate value based on multiple conditions of other columns

[Anonymous]

Hi,

I am stuck at this problem to calculate sum based on multiple condition. I have this weekly table. I want to create a measure or calculated  column - if the sum of value contributed for variable group "base" is less than 0 then sum the values of value contributed column where the variable group is not "base". Divide each variable group value contributed with the sum of 

value contributed where the variable group is not "base". If it's greater than 0 then copy the value of value contributed column value as it is. Here's an example- for week 10-feb-2020 the sum of value contributed column for variable group "base" is -26 (50-157+81) which is less than 0 so, I want to create a column highlighted where the formula should be  77/(77+40+1)*actual value = (77/118)*91 = 59.3

For this week 13-Apr-20, the sum of value contributed for base is greater than 0 so, I just want to copy the value of value contributed column as it is.

Table example-

week	value	variables	variable group	pred value	actual value	proportion of value	value contributed	measure/column
10-Feb-20	54	feb	base	99	91	0.55	50	0
10-Feb-20	-170	comp	base	99	91	-1.73	-157	0
10-Feb-20	83	a	offline	99	91	0.85	77	(77/(77+40+1))*91
10-Feb-20	44	b	online	99	91	0.44	40	(40/(77+40+1))*91
10-Feb-20	1	c	offline	99	91	0.01	1	(1/(77+40+1))*91
10-Feb-20	87	intercept	base	99	91	0.89	81	0
10-Feb-20	0	d	offline	99	91	0	0	0
10-Feb-20	0	e	online	99	91	0	0	0
10-Feb-20	0	f	online	99	91	0	0	0
10-Feb-20	0	g	online	99	91	0	0	0
10-Feb-20	0	h	online	99	91	0	0	0
10-Feb-20	0	i	base	99	91	0	0	0
10-Feb-20	0	dec	base	99	91	0	0	0
10-Feb-20	0	aug	base	99	91	0	0	0
10-Feb-20	0	nov	base	99	91	0	0	0
10-Feb-20	0	may	base	99	91	0	0	0
10-Feb-20	0	jan	base	99	91	0	0	0
10-Feb-20	0	oct	base	99	91	0	0	0
10-Feb-20	0	sep	base	99	91	0	0	0
10-Feb-20	0	jul	base	99	91	0	0	0
10-Feb-20	0	jun	base	99	91	0	0	0
10-Feb-20	0	apr	base	99	91	0	0	0
13-Apr-20	193	apr	base	279	440	0.69	304	304
13-Apr-20	55	cov	base	279	440	0.2	86	86
13-Apr-20	-181	comp	base	279	440	-0.65	-286	-286
13-Apr-20	91	a	offline	279	440	0.33	144	144
13-Apr-20	28	b	online	279	440	0.1	44	44
13-Apr-20	6	c	offline	279	440	0.02	10	10
13-Apr-20	87	intercept	base	279	440	0.31	138	138
13-Apr-20	0	d	offline	279	440	0	0	0
13-Apr-20	0	e	online	279	440	0	0	0
13-Apr-20	0	f	online	279	440	0	0	0
13-Apr-20	0	g	online	279	440	0	0	0
13-Apr-20	0	h	online	279	440	0	0	0
13-Apr-20	0	dec	base	279	440	0	0	0
13-Apr-20	0	aug	base	279	440	0	0	0
13-Apr-20	0	nov	base	279	440	0	0	0
13-Apr-20	0	may	base	279	440	0	0	0
13-Apr-20	0	jan	base	279	440	0	0	0
13-Apr-20	0	oct	base	279	440	0	0	0
13-Apr-20	0	sep	base	279	440	0	0	0
13-Apr-20	0	jul	base	279	440	0	0	0
13-Apr-20	0	jun	base	279	440	0	0	0
13-Apr-20	0	feb	base	279	440	0	0	0

I hope the question is clear if not please let me know.

Any help would be much appreciated.

[Adescrit · Accepted solution]

Hi @Anonymous 

How about the formula below:

Calculation =
  //Calculate total value contributed for the current week where variable group is base
VAR __BasePerWeek =
    CALCULATE (
        SUM ( 'Table'[value contributed] ),
        ALL ( 'Table' ),
        'Table'[week] = EARLIER ( 'Table'[week] ),
        'Table'[variable group] = "base"
    )
  //Calculate total value contributed for the current week where variable group is not base
VAR __NotBasePerWeek =
    CALCULATE (
        SUM ( 'Table'[value contributed] ),
        ALL ( 'Table' ),
        'Table'[week] = EARLIER ( 'Table'[week] ),
        'Table'[variable group] <> "base"
    )
RETURN
    IF (
        __BasePerWeek < 0,
        DIVIDE ( 'Table'[value contributed], __NotBasePerWeek ) * 'Table'[actual value],
        'Table'[value contributed]
    )

Let me know if it works for you, and please mark myt response as a solution if so.