Excel Pivot MDX

[gavs73]

Hi

Would appreciate any help with this, I’m struggling with the MDX trying to calculate BMI ( Body Mass Index = Weight / Height Squared ), as a Calculated Member in PivotTable Tools -> Analyze -> OLAP Tools -> MDX Calculated Member.

I believe I need an IF, as Height might be 0 in some cases, and hence /DIV0 issues.

IF [Height] IS > 0 THEN ( [Weight] / ( [Height] * [Height] ) ELSE 0 END IF

Thanks in advance for any help.

Thanks
Gav

 

[AFPathfinder]

You can put the IF statement into the calculated field when you create it (Analyze tab → Fields, Items, & Sets drop-down → Calculated Field...). However, I'd assume you would want the BMI to be calculated in your dataset before it gets to the pivot table unless you're looking for an average BMI.