Custom Subtotal over attribute elements

Hello there!

Today let’s talk about creating subtotal over attribute elements.

Let’s suppose I have 2 attributes and 1 metric in a report template: Year (in columns) , Category (in rows) and Profit(in columns). Let’s say that I need to calculate the growth of Profit among 2011 and 2012 for each category. How can I do that?

One thing that could be done is to create 2 metrics and insert a conditional “2011” for the first metric and “2012” for the second metric. Using this procedure I will have some problems to calculate the growth over different years. I would have to create another metrics with another filters for each different year.

That isn’t a good approach. Instead, you can create a subtotal that gets the first year and the last year automatically and then calculates that growth for me.

To do that you have to:

1. Go to Project Objects -> Subtotals


2. Create a new Subtotal with this formula:


Here I’m using the Last and First function to get the first year and the last year of my report. The trick here is to use the SortBy = Year. If you don’t use this SortBy, MicroStrategy won’t get the correct value for First and Last. The “@” indicates the place where Profit Metric will be inserted.

If you can’t see the functions parameters, just go to View ->and click on “Show Functions Parameters”, just like the image below:


3. Go to Profit metric and include this subtotal to the available subtotals:


4. Create a new report using these objects below:


5. Go to Data-> Subtotals and add check the Growth subtotal.


6. Now when you run your report, MicroStrategy will divide 2012/2011 just like we wanted.



That’s it.

Hope it helps.


God bless you.

2 thoughts on “Custom Subtotal over attribute elements

  1. Preeti

    I have a similar kind or requirement.
    With year,when I am adding the month attribute to column then the growth value is not coming correctly.By keeping only year growth value is coming correctly,but not after the adding the month attribute to column.


    1. Felipe Vilela Post author

      hi Preeti. First, check the dimentionality of your Metric to make sure that it can calculate at a Month level. also, check the sql to investigate how mstr is calculating your growth.



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.