Multiple formulas and lookups in one calculated column.

[HarryR1]

Hi

I hope someone can help with a problem I have in PowerPivot.

I have two tables. on called "project plan" and another called "cost to complete". The project names are listed the same in both tables.

In the Project Plan table I have a dimension called "Plan Required" that is True/False, indicating if a plan is required or not. There is also a measure called "Remaining Cost"

In the Cost to Complete table I have other measures called Measure A and Measure B.

In the Cost to Complete table I want to make a new calculated column that says:

1. If the value of (Project Plan[Plan Required]) = True, then perform a calculation: Measure B subtracted from Measure A.

2. If the value of (Project Plan[Plan Required]) = False, then fetch the value from (Project Plan[Remaining Cost])

I hope that makes sense.

Basically I want to fetch a value from linked table if the value of [Plan Required] is false, but if it's true, then perform a calculation.

I appreciate any help offered.

Harry

 

[ImkeF]

Hi Harry,
I think you need a measure, if your MeasureA and MeasureB are real measures and not just columns. At least I don't know a way to "get back" the row context if you drag them back into columns. So you'd take a measure like this instead:

NewMeasure:=IF(IF(HASONEVALUE(Project Plan[Plan Required]), VALUES(Project Plan[Plan Required]))="True", MeasureB-MeasureA, MAX(Project Plan[Remaining Cost]))

 

[HarryR1]

Hi Harry,
I think you need a measure, if your MeasureA and MeasureB are real measures and not just columns. At least I don't know a way to "get back" the row context if you drag them back into columns. So you'd take a measure like this instead:

NewMeasure:=IF(IF(HASONEVALUE(Project Plan[Plan Required]), VALUES(Project Plan[Plan Required]))="True", MeasureB-MeasureA, MAX(Project Plan[Remaining Cost]))

Hi

Thanks for the input. Using aactual measures from the cube may be a problem as the values I need to fetch are only available because the have been calculated from an original measure that wasn't doing the job. I think some cube engineering is required instead of trying to force it with PowerPivot.

Thanks