Add a subtotal with “Rollup”

If you use “Summarize” you can add a subtotal with the use of “Rollup”.

In the code below there is an example on how this can be done.

EVALUATE
FILTER (
    SUMMARIZE (
        'Internet Sales',
        ROLLUP ( 'Date'[Month Name] ),
        'Date'[Calendar Year],
        "Subtotal?", ISSUBTOTAL ( 'Date'[Month Name] ),
        "Sales", SUM ( 'Internet Sales'[Sales Amount] ),
        "Quantity", SUM ( 'Internet Sales'[Order Quantity] )
    ),
    'Date'[Calendar Year] = 2007
)

This query will give you sales and quantity per month. But you will also have a row with subtotal. To check if the row is a “Rollup” we can use the “ISSUBTOTAL”. You also have something called “Rollupgroup”.

In MDX you can write something like this to get a subtotal.

select {[Measures].[Internet Total Sales],[Measures].[Internet Total Units]} on 0,
[Date].[Calendar Year].&[2007] * [Date].[Month Name].AllMembers on 1
from [Internet Operation]

The subtotal will be in the “all” row. Depending on what you called the all level 🙂 You do not have any functionality like ISSUBTOTAL as far as I know.

Leave a Reply

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

WordPress.com Logo

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

Facebook photo

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

Connecting to %s