# Conditional Calculation based upon Dimension field value - PowerPivot

### Question

• Goal:
A field in a dimension has a 'Y' or 'N'.

If it says 'Y' then: 100
If it says 'N' then: CALCULATE(DIVIDE([Job Line Costs To Date],SUM('JOB COST DETAILS'[Job Line Estimate Cost Amount])),Job[Job Comp Y or N]="N")

Saturday, September 14, 2013 1:20 PM

• You can create two "child" calculated measures.  One for Y that always equals 100.  The other for N with your calculation above.

Then wrap them in a "parent" calculated measure that uses conditional logic, like IF or SWITCH:

ConditionalCalc:=IF(MAXA(Table[Dimension]) = "Y", CalcMeasureY, CalcMeasureN)

ConditionalCalc2:=SWITCH(MAXA(Table[Dimension]),"Y",CalcMeasureY, "N", CalcMeasureN, BLANK())
Let me know if that helps.

Brent Greenwood, MS, MCITP, CBIP
http://brentgreenwood.blogspot.com

Sunday, September 15, 2013 2:27 AM

### All replies

• You can create two "child" calculated measures.  One for Y that always equals 100.  The other for N with your calculation above.

Then wrap them in a "parent" calculated measure that uses conditional logic, like IF or SWITCH:

ConditionalCalc:=IF(MAXA(Table[Dimension]) = "Y", CalcMeasureY, CalcMeasureN)

ConditionalCalc2:=SWITCH(MAXA(Table[Dimension]),"Y",CalcMeasureY, "N", CalcMeasureN, BLANK())
Let me know if that helps.

Brent Greenwood, MS, MCITP, CBIP