How do I make this calculated measure roll up properly in SSAS?

Question

I have a calculated measure in Analysis Services that returns the average daily revenue for a location based on their last three periods. Periods are location defined lengths of time, so one location may close their periods monthly, and another may close their periods quarterly.

To return the average daily revenue I have a factPeriods table that returns the revenue for a period, and the number of days that contribute to the period. The following MDX returns the correct values at the location level, ([Measures].[Days In AR Charges]) / ([Measures].[Days In AR Days]).

The problem is when I roll up to higher levels, say I want to return the average daily revenue by state, the calculation is incorrect because it first aggregates the days contributing to the periods, then divides. I want the averaging to happen at the lowest level first, then to sum the results of the daily average revenue. Here is an example of what happens vs what I would like to happen.

Say I have only two locations, A and B. A closes monthly, B closes quarterly. The results for the last close are:

Location / Revenue / Days contributing to revenue

Location A / $3000 / 30 days Location B / $1800 / 90 days

Location A is generating $100/day revenue, Location B is generating only $20/day in revenue. My total should be $120 day. Instead, it would return $40/day. Here is what happens at the calculation level.

I would like the formula to be in pseudocode SUM(([Measures].[Days In AR Charges]) / ([Measures].[Days In AR Days]))

but it is actually giving me (SUM([Measures].[Days In AR Charges])) / (SUM([Measures].[Days In AR Days]))

Can anyone help me determine the appropriate formula to make this work as desired? It should end up being $100/day + $20/day, not $4800/120 days. I have a suspicious I may need to use descendants but I am not clear on exactly how I would use them, or if that is even the right direction to go.

Answer 1

My immediate solution is going to be to calculate the average daily revenue in the ETL process, then SSAS will just sum the value. I would still love to see the MDX solution to the problem though if anyone knows it.

Answer 2

This is simply a matter of specifying the proper "solve order" with your calculations. I can't give you specific MDX to solve your problem since I don't know what your cube structure is, but look into "solve order" in the docs or via Google. This is exactly what "solve order" is supposed to address. You're going to basically tell Analysis Services what to do first.

Answer 3

The easiest way to force the calculation to happen at the leaf level and then rollup is to move it before the CALCULATE; statment in your MDX script.

Normally you should leave the CALCULATE; should be the first statement in the script, but I think the situation you describe is one of the exceptions where it is justified to have a calculated member before the CALCULATE;

Answer 4

Don't use SSAS calculations if you can get the number you want in ETL or processing as it will perform better.

In this case I would add a DSV calculation to do the division then allow SSAS to sum as normal.

Answer 5

Hello,

Did you ever figure out how to make this work? I have a similar problem where I want the grand total to just add up the sum of the parts instead of performing the calculation again at the grand total level.

Thanks,

JMAC

Answer 6

Working with Custom Member Formulas

http://msdn.microsoft.com/en-us/library/ms174474%28SQL.90%29.aspx