none
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

Answers

  • 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
    // Please mark correct answers and helpful posts //
    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
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com


    Sunday, September 15, 2013 2:27 AM
  • that's about what I ended up doing.  Maybe Microsoft will do something like that in the future.... a shame that it should have to take two formulas to create one with a conditional branch.Thanks for the response.

    Monday, September 16, 2013 6:45 AM