I had a requirement recently where the customer wanted a measure to be formatted differently, based on its value.

In this case, it was two numeric measures with 2 decimal point precision, and for cases where there were no decimal digits, it was supposed to look like an integer.

I looked for a MOD function in VBA or SSAS that can be used here, but came up empty 😦

I used the SCOPE statement to override the default formatting

SCOPE({[Measures].[Sales], [Measures].[Cost]});
FORMAT_STRING(This)= IIF(Round([Measures].CurrentMember.VALUE, 2)=[Measures].CurrentMember.VALUE, “#,###;;0;”, “#,###.00;;0;”);
END SCOPE;

For the measures in question, the SCOPE statement compares its ROUNDed value to its actual value. If these are equal, that means that the number is a whole number, and has to be formatted without decimal places shown. Hence the FORMAT_STRING expression.

Advertisements