Suggestions to Optimize DAX RollUp and SubTotal query

[abhiram342]

Hi Team,

We want to get the rollup and subtotals across different dimensions .Can you please tell if there is way to optimize it? 

EVALUATE
var t1 = CALCULATETABLE(
    SUMMARIZE(
        'Fact Sales',
        ROLLUP (
                'Customer'[Name],
                'Customer'[Geography],
                'Customer'[Region],
                'Customer'[Industry],
                'Store'[Subsidiary]),
        "Name total",           ISSUBTOTAL ('Customer'[Name]),
        "Geography total",       ISSUBTOTAL ('Customer'[Geography]),
        "Region total",          ISSUBTOTAL ('Customer'[Region]),
        "Industry total",        ISSUBTOTAL ('Customer'[Industry]),
        "Subsidiary total",      ISSUBTOTAL ('Store'[Subsidiary]),
        "Metric Name", NAMEOF('Fact Sales'[Sales]),
        "Metric Value", [Sales]),
        TREATAS ({ "FY22" }, 'Date'[Fiscal Year])
    )
RETURN t1

Thanks,

Abhiram

[Anonymous]

HI @abhiram342,

In my opinion. I'd like to suggest you create a summary table with all category and value fields without rollup.
Then you can use this table to create a matrix visual and use the category fields on row and column, value field to matrix value and these values will auto aggregated on subtotal levels.

Regards.

Xiaoxin Sheng