cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AlexisOlson
Memorable Member
Memorable Member

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

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.

AlexisOlson_2-1617814678242.png

 

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:

AlexisOlson_5-1617819625878.png

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

AlexisOlson_4-1617819503246.png

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:

AlexisOlson_3-1617815113755.png

 

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. S
tated 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?

0 REPLIES 0

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors