Changing displayed meaures as user drills down within Matrix?

[Anonymous]

Hello,

I need to show sales by a date hierarchy (year > month > day) which displays different measures depending upon the level of the hierarchy the user drills to within a matrix. Is this possible?

I have currently set this up across 3 different matrix objects.

So the yearly view

Policy Count Change vs Prev Year = sum('LGIDW SOP'[Policy Volume]) - CALCULATE(sum('LGIDW SOP'[Policy Volume]), filter( all('System Submitted Date'), 'System Submitted Date'[Calendar YearNumber] = max('System Submitted Date'[Calendar YearNumber])-1))

% Policy Count Change vs Prev Year = divide([Policy Count Change vs Prev Year], [Total Policy Count])

Monthly

Policy Count Change vs Prev Month = sum('LGIDW SOP'[Policy Volume]) - CALCULATE(sum('LGIDW SOP'[Policy Volume]), filter( all('System Submitted Date'), 'System Submitted Date'[Calendar Year] = max('System Submitted Date'[Calendar Year]) && 'System Submitted Date'[Calendar MonthNumber] = max('System Submitted Date'[Calendar MonthNumber])-1 ))

% Policy Count Change vs Prev Month = divide([Policy Count Change vs Prev Month], [Total Policy Count])

Weekly

Policy Count Change vs Prev Week = sum('LGIDW SOP'[Policy Volume]) - CALCULATE(sum('LGIDW SOP'[Policy Volume]), filter( all('System Submitted Date'), 'System Submitted Date'[Calendar Year] = max('System Submitted Date'[Calendar Year]) && 'System Submitted Date'[Calendar WeekNumber] = max('System Submitted Date'[Calendar WeekNumber])-1 && 'System Submitted Date'[WeekDayNumber] = max('System Submitted Date'[WeekDayNumber])))

% Policy Count Change vs Prev Week = divide([Policy Count Change vs Prev Week], [Total Policy Count])

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

We can use HASONEVALUE to identify the context. The formula below is based on your data here. Please try it out.

New Measure =
IF (
    HASONEVALUE ( 'System Submitted Date'[WeekDayNumber] ),
    SUM ( 'LGIDW SOP'[Policy Volume] )
        - CALCULATE (
            SUM ( 'LGIDW SOP'[Policy Volume] ),
            FILTER (
                ALL ( 'System Submitted Date' ),
                'System Submitted Date'[Calendar Year]
                    = MAX ( 'System Submitted Date'[Calendar Year] )
                    && 'System Submitted Date'[Calendar WeekNumber]
                        = MAX ( 'System Submitted Date'[Calendar WeekNumber] ) - 1
                    && 'System Submitted Date'[WeekDayNumber]
                        = MAX ( 'System Submitted Date'[WeekDayNumber] )
            )
        ),
    IF (
        HASONEVALUE ( 'System Submitted Date'[Calendar MonthNumber] ),
        SUM ( 'LGIDW SOP'[Policy Volume] )
            - CALCULATE (
                SUM ( 'LGIDW SOP'[Policy Volume] ),
                FILTER (
                    ALL ( 'System Submitted Date' ),
                    'System Submitted Date'[Calendar Year]
                        = MAX ( 'System Submitted Date'[Calendar Year] )
                        && 'System Submitted Date'[Calendar MonthNumber]
                            = MAX ( 'System Submitted Date'[Calendar MonthNumber] ) - 1
                )
            ),
        IF (
            HASONEVALUE ( 'System Submitted Date'[Calendar YearNumber] ),
            SUM ( 'LGIDW SOP'[Policy Volume] )
                - CALCULATE (
                    SUM ( 'LGIDW SOP'[Policy Volume] ),
                    PREVIOUSYEAR ( 'System Submitted Date'[Calendar date] )
                )
        )
    )
)

Best Regards,

Dale

[Anonymous]

@v-jiascu-msft thank you! I have came across that function previously but never had the chance to use it.

That works really well!