Using the View Dimension for Calculations

We had a requirement to calculate dollars per unit. There are several ways this could be accomplished including a basic business rule or a member formula. But what made it a bit tricky was that it needed to calculate not just on the base members, but also on the parents. We were able to do this in the ASO reporting cube within the View dimension.

Often the View dimension is used for Periodic, QTD, and YTD views of data. QTD and YTD members have formulas to sum the data as needed. We left that functionality untouched and added another hierarchy with members to do dollars per unit calculations.

The MDX script logic is a bit confusing at first (at least to me it was), but makes sense once you get going.

The first calculation is for descendants of WF_Sales. MDX does not have a function for descendants, instead we use IsAncestor.
To include the member WF_Sales (the parent), we add INCLUDEMEMBER.
The formula is basic A / B where A is the sales account and B is the unit stat account. The sales account is the current member from the WHEN statement. The unit account is also on a specific cost centre and currency.
Both sides of the formula have to reference the View member Periodic. This is important and took me some troubleshooting to get it right. Without reference to the View member, the formula will not work.

The third calculation is different than the first two. We do not want to calculate descendants, we only want the parent member.
With the account Operating Costs, we also want to specify a cost centre. The formula will only calculate when these two conditions are met.

Let’s check the results. Notice that we have View members Periodic and DollarsPerUnit.

The first formula was for Sales divided by Stat Sales. Each member, whether a parent or base, calculated correctly.
Similarly, the second formula of Raw Mat divided by Stat P0216 calculated correctly for parents and base members.
The third formula was only on a parent, and at a specific cost centre. It also calculated correctly.
The fourth formula for Freight divided by Stat Sales is correct.

To further show the robustness of these View member formulas, let’s check it out for YTD. We have a separate member for the YTD calculations. It’s identical to the other member formula except it references View member YTD.