Forum Discussion

Helper III

6 years ago

Solved

Matrix Table Drill Down

I am working on a matrix table to display the budget and actual data. Budget and actual data are from two separate fact tables, they are relationship with region dimension table. However, budget ...

Need Help

Icey

6 years ago

Hi WendyDUFE ,

Try this:

Measure =
VAR Value_ =
    SWITCH (
        TRUE (),
        ISINSCOPE ( 'Dimension'[District] ),
            CALCULATE (
                SUM ( Budget[Amount] ),
                USERELATIONSHIP ( Budget[District], 'Dimension'[District] )
            ),
        ISINSCOPE ( 'Dimension'[City] ),
            CALCULATE (
                SUM ( Budget[Amount] ),
                USERELATIONSHIP ( Budget[City], 'Dimension'[City] )
            ),
        ISINSCOPE ( 'Dimension'[Province] ),
            CALCULATE (
                SUM ( Budget[Amount] ),
                USERELATIONSHIP ( Budget[Province], 'Dimension'[Province] )
            ),
        ISINSCOPE ( 'Dimension'[Country] ),
            CALCULATE (
                SUM ( Budget[Amount] ),
                USERELATIONSHIP ( Budget[Country], 'Dimension'[Country] )
            )
    )
RETURN
    IF ( ISBLANK ( Value_ ), "-", Value_ )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Matrix Table Drill Down.pbix70 KB

Icey

6 years ago

Hi WendyDUFE ,

Try this:

Budget Amount =
VAR Value_ =
    SWITCH (
        TRUE (),
        ISINSCOPE ( 'Dimension'[District] ),
            CALCULATE (
                SUM ( Budget[Amount] ),
                USERELATIONSHIP ( Budget[District], 'Dimension'[District] )
            ),
        ISINSCOPE ( 'Dimension'[City] ),
            CALCULATE (
                SUM ( Budget[Amount] ),
                USERELATIONSHIP ( Budget[City], 'Dimension'[City] )
            ),
        ISINSCOPE ( 'Dimension'[Province] ),
            CALCULATE (
                SUM ( Budget[Amount] ),
                USERELATIONSHIP ( Budget[Province], 'Dimension'[Province] )
            ),
        ISINSCOPE ( 'Dimension'[Country] ),
            CALCULATE (
                SUM ( Budget[Amount] ),
                USERELATIONSHIP ( Budget[Country], 'Dimension'[Country] )
            ),
        SUM ( Budget[Amount] ) // ----------------added--------------------------//
    )
RETURN
    IF ( ISBLANK ( Value_ ), "-", Value_ )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

FarhanAhmed

Community Champion

6 years ago

You can create your Budget Measure to include "ISINSCOPE" to exclude calculations on particular Column/Hierarchy

https://docs.microsoft.com/en-us/dax/isinscope-function-dax

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

https://www.sqlbi.com/articles/distinguishing-hasonevalue-from-isinscope/

WendyDUFE

Helper III

6 years ago

Hi FarhanAhmed

I refered to this method ( https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/)to make adjustment, and finally got a matrix table as below:

In the fact table, if the data is associated with district dimension, then the city and province level matrix table will not do the summarization and display the total amount of district, do you know how to solve this ? It is supposed to have the upper level hierachy summerize the lower hierachy's amount, right ?

Icey

Community Support

6 years ago

Hi WendyDUFE ,

Try this:

Measure =
VAR Value_ =
    SWITCH (
        TRUE (),
        ISINSCOPE ( 'Dimension'[District] ),
            CALCULATE (
                SUM ( Budget[Amount] ),
                USERELATIONSHIP ( Budget[District], 'Dimension'[District] )
            ),
        ISINSCOPE ( 'Dimension'[City] ),
            CALCULATE (
                SUM ( Budget[Amount] ),
                USERELATIONSHIP ( Budget[City], 'Dimension'[City] )
            ),
        ISINSCOPE ( 'Dimension'[Province] ),
            CALCULATE (
                SUM ( Budget[Amount] ),
                USERELATIONSHIP ( Budget[Province], 'Dimension'[Province] )
            ),
        ISINSCOPE ( 'Dimension'[Country] ),
            CALCULATE (
                SUM ( Budget[Amount] ),
                USERELATIONSHIP ( Budget[Country], 'Dimension'[Country] )
            )
    )
RETURN
    IF ( ISBLANK ( Value_ ), "-", Value_ )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Matrix Table Drill Down.pbix70 KB

WendyDUFE

Helper III

6 years ago

Thanks Icey

I have accepted this solution, but one small item need your help, I noticed the total row in the buttom of the table is blank, is there a way to have the total amount displayed ?

Thank you

Icey

Community Support

6 years ago

Hi WendyDUFE ,

Try this:

Budget Amount =
VAR Value_ =
    SWITCH (
        TRUE (),
        ISINSCOPE ( 'Dimension'[District] ),
            CALCULATE (
                SUM ( Budget[Amount] ),
                USERELATIONSHIP ( Budget[District], 'Dimension'[District] )
            ),
        ISINSCOPE ( 'Dimension'[City] ),
            CALCULATE (
                SUM ( Budget[Amount] ),
                USERELATIONSHIP ( Budget[City], 'Dimension'[City] )
            ),
        ISINSCOPE ( 'Dimension'[Province] ),
            CALCULATE (
                SUM ( Budget[Amount] ),
                USERELATIONSHIP ( Budget[Province], 'Dimension'[Province] )
            ),
        ISINSCOPE ( 'Dimension'[Country] ),
            CALCULATE (
                SUM ( Budget[Amount] ),
                USERELATIONSHIP ( Budget[Country], 'Dimension'[Country] )
            ),
        SUM ( Budget[Amount] ) // ----------------added--------------------------//
    )
RETURN
    IF ( ISBLANK ( Value_ ), "-", Value_ )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

Matrix Table Drill Down

Related content

Matrix table

Matrix table

KPI level matrix table

Matrix/Table Drill Thrugh Sum of Flag

Matrix table in Power bi