A few months ago I was sitting in on an MDX class Stacia Misner was teaching. The students were playing with their MDX and wanted to know if it was possible to have a calculation that would show the running sum over products, ordered by the gross profit margin; i.e. we would list the product subcategories on rows, sorted by their gross profit margin, and then have a running total down this list. This mirrored a calculation they had to have in their business.
Stacia (aka mom 🙂 ) was a bit busy teaching the class and didn’t want to get too bogged down on this problem (although she REALLY REALLY wanted to figure it out) so she tossed it to me after some quick initial attempts were unsuccessful. She had never seen anyone do this, and knew that MDX had rolling sums over time, but this was over something other than time. This is what I came up with.
This is using the Adventure Works cube.
set Q as
,[Measures].[Reseller Gross Profit Margin], ASC)
,[Measures].[Reseller Sales Amount])
member [Measures].[level] as rank([Product].[SubCategory].CurrentMember,Q)
member [Measures].[Running] as Sum(
,[Measures].[Reseller Sales Amount]
, [Measures].[Reseller Sales Amount]
, [Measures].[Reseller Gross Profit Margin]}
from [Adventure Works]
I found it easier to just go ahead and create my row set with a with statement, rather than keep repeating it throughout the query. It made things neater. The set Q is straightforward enough; I have a nonempty set of Product Subcategories, and I order them by the gross profit margin as requested. NB: the sort is the key thing here. This is where you decide how you want your total to “roll”, so to speak.
Next, I created a measure that gave me the rank of my current subcategory out of the overall set Q. Yay rank() function!
Finally, I create my running measure which will calculate the running total. What I figured out is, once I have my set ordered correctly, the running sum is just the sum of all the previous rows plus the current row. So if I can isolate my set down to those rows, I can just apply the sum over that. Well, thanks to the ordering I set, I can, thanks to the Head() function. I take the Head() of my set Q, where my integer value is [Measures].[Level]. So I have gotten exactly the rows I want the running sum of. I take the sum of that, and voila! Running total.
Just a simple query, but people seemed excited to have it, so I record it here for posterity.