cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
amorts Regular Visitor
Regular Visitor

Changing displayed meaures as user drills down within Matrix?

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])

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Changing displayed meaures as user drills down within Matrix?

Hi @amorts,

 

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support Team
Community Support Team

Re: Changing displayed meaures as user drills down within Matrix?

Hi @amorts,

 

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
amorts Regular Visitor
Regular Visitor

Re: Changing displayed meaures as user drills down within Matrix?

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

 

That works really well!