Handling Subtotals for Pre-Calculated (Non-Additive) Measures

[AlexisOlson]

I'd like to share a neat pattern that I've discovered recently.

Motivation

I'm building a report where I'd like to use a small amount of summarized data from another report I've built that has a large and complex data model. In my particular case, I want to pull IRR values at several levels of granularity. The large complex model has measures that allow me to dynamically generate summary tables for whatever configuration of row and column granularities I choose.

Here's an example with Category and Subcategory on the rows and Group on the columns.

In the new report I'm building, this matrix is exactly what I need. I don't need the flexibility to dynamically choose other granularities to report on and I don't want to bog down my report with all the memory or computational overhead needed to do so. As a result, I've chosen to import this data into my new report by querying the existing complex data model. (I do this by connecting to the complex model dataset as an Analysis Server with DAX query. Refer to this question for a bit more detail.)

Query

My first thought is to write a simple query like this

EVALUATE
SUMMARIZECOLUMNS (
    ComplexModel[Category],
    ComplexModel[Subcategory],
    ComplexModel[Group],
    "IRR", [IRR]
)

The result looks like this:

This works fine for all of the non-bold numbers in the matrix above but there's no way to generate the subtotals and grand totals (all the bold numbers in the matrix) since it's only returning the IRR at the lowest level of granularity and these returns cannot be summed or averaged or otherwise aggregated/combined to get the subtotals I'm interested in.

My solution to this is to use the handy ROLLUPADDISSUBTOTAL functionality within SUMMARIZECOLUMNS.

EVALUATE
SUMMARIZECOLUMNS (
    ROLLUPADDISSUBTOTAL (
        ComplexModel[Category], "IsCategoryRollup",
        ComplexModel[Subcategory], "IsSubcategoryRollup"
    ),
    ROLLUPADDISSUBTOTAL ( ComplexModel[Group], "IsGroupRollup" ),
    "IRR", [IRR]
)

Note: Category and Subcategory are together since the latter is always a subset of the former.

The result looks like this

Notice the rows with blanks in the first three columns, which correspond to subtotals over those dimensions.

This updated query has all of the values we need but how do we use it?

Measure

Suppose we use a simple measure like this:

Simple Measure = SELECTEDVALUE ( Summary[IRR] )

This actually gets us all of the values we need but doesn't display them like I want (i.e. blank value rows and columns instead of subtotal rows and columns.

This is what it looks like if we use it in a visual with columns from the Summary table:

Savvy DAX folks are probably aware of the function ISINSCOPE I'll use to resolve this. If not, check the related articles linked from its DAX Guide page. My first attempts at this measure looked like some variation of a measure like this:

Verbose Measure =
IF (
    ISINSCOPE ( Summary[Subcategory] ),
    IF (
        ISINSCOPE ( Summary[Group] ),
        CALCULATE (
            SELECTEDVALUE ( Summary[IRR] ),
            Summary[IsSubcategoryRollup] = FALSE (),
            Summary[IsCategoryRollup] = FALSE (),
            Summary[IsGroupRollup] = FALSE ()
        ),
        CALCULATE (
            SELECTEDVALUE ( Summary[IRR] ),
            Summary[IsSubcategoryRollup] = FALSE (),
            Summary[IsCategoryRollup] = FALSE (),
            Summary[IsGroupRollup] = TRUE ()
        )
    ),
    IF (
        ISINSCOPE ( Summary[Category] ),
        IF (
            ISINSCOPE ( Summary[Group] ),
            CALCULATE (
                SELECTEDVALUE ( Summary[IRR] ),
                Summary[IsSubcategoryRollup] = TRUE (),
                Summary[IsCategoryRollup] = FALSE (),
                Summary[IsGroupRollup] = FALSE ()
            ),
            CALCULATE (
                SELECTEDVALUE ( Summary[IRR] ),
                Summary[IsSubcategoryRollup] = TRUE (),
                Summary[IsCategoryRollup] = FALSE (),
                Summary[IsGroupRollup] = TRUE ()
            )
        ),
        IF (
            ISINSCOPE ( Summary[Group] ),
            CALCULATE (
                SELECTEDVALUE ( Summary[IRR] ),
                Summary[IsSubcategoryRollup] = TRUE (),
                Summary[IsCategoryRollup] = TRUE (),
                Summary[IsGroupRollup] = FALSE ()
            ),
            CALCULATE (
                SELECTEDVALUE ( Summary[IRR] ),
                Summary[IsSubcategoryRollup] = TRUE (),
                Summary[IsCategoryRollup] = TRUE (),
                Summary[IsGroupRollup] = TRUE ()
            )
        )
    )
)

Note: You may notice that there are only six scope combinations compared to the total possible 2³ = 8 for the three dimensions, Category, Subcategory, Group. The reason is that I have excluded the cases where Category is rolled up but Subcategory is not.

This measure does work but it's ugly and tedious to write. Fortunately, I noticed the relationship between Is-In-Scope and Is-a-Rollup. Whenever a dimension is in scope, select the rows that are not rollups of that dimension. Stated in reverse, select the rollup rows when a dimension is not in scope. This leads to a much more elegant version of the measure:

Smarter Measure =
VAR IsCategoryRollup = NOT ISINSCOPE ( Summary[Category] )
VAR IsSubcategoryRollup = NOT ISINSCOPE ( Summary[Subcategory] )
VAR IsGroupRollup = NOT ISINSCOPE ( Summary[Group] )
RETURN
    CALCULATE (
        SELECTEDVALUE ( Summary[IRR] ),
        Summary[IsCategoryRollup] = IsCategoryRollup,
        Summary[IsSubcategoryRollup] = IsSubcategoryRollup,
        Summary[IsGroupRollup] = IsGroupRollup
    )

Using this measure in a matrix visual with Summary[Category], Summary[Subcategory] on the rows and Summary[Group] on the columns now looks just like the original matrix visual within the Complex Model.

Neat, eh?

[janstrauss1]

Hi @AlexisOlson ,

Following up on our discussionHow to sum / aggregate on the right parent child hierarchy level using a hierarchy slicer? I wondered if you have you tried to visualize the results of such approach using other visuals than the matrix visual (e.g. line or bar charts)?

[KN_HEALT47]

I wrote this measure with the help of a forum user: 

YTD Budget =

IF(OR(ISINSCOPE(Expenses[partner_name]),ISINSCOPE(Expenses[VendorJA])), BLANK(),CALCULATE(SUM(Budget[Amount]), MONTH(Budget[Merged Month Year]) <= MONTH(TODAY())-1))

and the result is this:

I like that the invoice detail is blank for the parts of the budget that do not have this detail the problem is that the YTD budget is aggregating on repeat for each account line ( see 50001 Payroll). Idealy the total budget YTD would populate for 50001 payroll only and so on for each account and 493,604.01 would only be the total at the bottom. Both budget and Actuals share the account detail, only budget does not go further down into invoice and vendor name. Based on your post above I think it could help with this issue but not sure how to execute given limited knowledge on difference in query, measure, and use of variables. Any suggestions? thanks in advance.

[AlexisOlson]

This seems like a different problem, like, maybe your Budget table doesn't have a working relationship with the Expenses table.

[KN_HEALT47]

Does this work if your using a measure of a query instead of a query in the first step?