Conditional Measure with IF

[Anonymous]

Hello,

I want to create a measure that calculates the % of execution based on some measure values. I also have a column called Status and I want to calculate with a IF clause:

IF opc[Status]="X"

THEN SUM(opc[Custos])*100/sum(opc[Orçamento])

ELSE 100

I'm trying:

If (opc[Status]="X";SUM(opc[Custos])*100/sum(opc[Orçamento]);100)

Power BI doesn't recognize the column opc[Status].

[Anonymous · Accepted solution]

HI @Anonymous ,

You can't direct use columns in measure expressions, you need to add aggregate calculation on them to return summarized value and they can used in expressions.(e.g. sum, max, average, firstnonblank)
In addition, you can also use selectedvalue function to get current row contents value to compare with if statement conditions:

Measure =
VAR currStatus =
    SELECTEDVALUE ( opc[Status] )
RETURN
    IF ( currStatus = "X"; SUM ( opc[Custos] ) * 100 / SUM ( opc[Orçamento] ); 100 )

Regards,

Xiaoxin Sheng