Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Solved! Go to 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.
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.
User | Count |
---|---|
25 | |
11 | |
7 | |
6 | |
6 |
User | Count |
---|---|
30 | |
13 | |
11 | |
9 | |
7 |