Forum Discussion
Create conditional measure
Greetings,
I'm trying to create a conditional measure that sums up all "stage 1" values only if "stage 3" has a value for the row item.
The data below is of sample source data, but when importing the data, I have unpivot the Stage 1 through to Stage 3 columns.
| Item | Stage 1 | Stage 2 | Stage 3 |
| 1 | 100 | 100 | 100 |
| 2 | 100 | 100 | 100 |
| 3 | 100 | 100 | |
| 4 | 100 |
What sort of measure is best to calculate this?
Thanks in advance!
It would be easier if we could see the data as it exists in the tabular table. But if I understand your issue correctly the following psuedo code should work
= SUMX( table1 ,
IF( calculate(SUM(table1[Value]) , table1[Stage] = "Stage 3) > 0 ,
IF( table1[Stage] = "Stage 1", table1[Value] )
)
)Hi rgtanna ,
Please also try this one.
Measure 2 = VAR hasStage3Items = CALCULATETABLE ( VALUES ( Table1[Item] ), Table1[Stage] = "Stage 3" ) RETURN CALCULATE ( SUM ( Table1[Value] ), FILTER ( Table1, Table1[Item] IN hasStage3Items && Table1[Stage] = "Stage 1" ) )
Best Regards,
2 Replies
It would be easier if we could see the data as it exists in the tabular table. But if I understand your issue correctly the following psuedo code should work
= SUMX( table1 ,
IF( calculate(SUM(table1[Value]) , table1[Stage] = "Stage 3) > 0 ,
IF( table1[Stage] = "Stage 1", table1[Value] )
)
)Hi rgtanna ,
Please also try this one.
Measure 2 = VAR hasStage3Items = CALCULATETABLE ( VALUES ( Table1[Item] ), Table1[Stage] = "Stage 3" ) RETURN CALCULATE ( SUM ( Table1[Value] ), FILTER ( Table1, Table1[Item] IN hasStage3Items && Table1[Stage] = "Stage 1" ) )
Best Regards,