SSAS and percentage measures

Scenario

Let's say you have the following base measures that are the raw data held in the source system:

• ListPriceDollars
• StandardDiscountPercentage

The cube users require these two measures and an additional StandardDiscountPriceDollars measure to be available for use.

In this situation, it's easy to get stuck at the point of ensuring StandardDiscountPercentage displays sensible numbers at all aggregation levels. There are plenty of options to play with - aggregation functions, measure expressions, calculated members, etc. For people with more of a SQL than MDX background there's a lot to understand here. Throw in some null values and a parent-child relationship and you'll be torturing yourself trying to find the best way to represent this in SSAS.

Solution Option

One possibility is to completely avoid trying to aggregate percentages.

1. Change your source view or named query to calculate StandardDiscountPriceDollars (=Product.ListPriceDollars / (1 - (Product.StandardDiscountPercentage/100))
2. Don't use the raw StandardDiscountPercentage value in the cube - instead, create a simple replacement calculated member (= 1 - ([Measures].[Standard Discount Price Dollars] / [Measures].[List Price Dollars])

With a little bit of zero and null handling the job will be done.