Forum Discussion

Frequent Visitor

6 years ago

Solved

Matrix hierarchy sum issue

Hi all, I need some help with a hierarchy table, I need parents not to add the value of their children, the values that I bring from the base are already calculated and I need them to be displayed a...

Need Help

Tips and Tricks

v-deddai1-msft

6 years ago

Hi ainsua77 ,

Please refer to the measure below based on your sample data:

Measure =
IF (
    ISINSCOPE ( 'Table'[level4] ),
    SUM ( 'Table'[Visitor] ),
    IF (
        ISINSCOPE ( 'Table'[level3] ),
        CALCULATE (
            VALUES ( 'Table'[Visitor] ),
            FILTER ( ALL ( 'Table' ), 'Table'[Web] = MAX ( 'Table'[level3] ) )
        ),
        IF (
            ISINSCOPE ( 'Table'[level2] ),
            CALCULATE (
                VALUES ( 'Table'[Visitor] ),
                FILTER ( ALL ( 'Table' ), 'Table'[Web] = MAX ( 'Table'[level2] ) )
            ),
            CALCULATE (
                VALUES ( 'Table'[Visitor] ),
                FILTER ( ALL ( 'Table' ), 'Table'[Web] = MAX ( 'Table'[level1] ) )
            )
        )
    )
)

Please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/Edz52VpyP21Fvq7ZvCowuuQBIUOZ3N8A7Hurs7L2vAeqAw?e=gxcGuw

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

Best Regards,

Dedmon Dai

v-deddai1-msft

Community Support

6 years ago

Hi ainsua77 ,

Please refer to the measure below based on your sample data:

Measure =
IF (
    ISINSCOPE ( 'Table'[level4] ),
    SUM ( 'Table'[Visitor] ),
    IF (
        ISINSCOPE ( 'Table'[level3] ),
        CALCULATE (
            VALUES ( 'Table'[Visitor] ),
            FILTER ( ALL ( 'Table' ), 'Table'[Web] = MAX ( 'Table'[level3] ) )
        ),
        IF (
            ISINSCOPE ( 'Table'[level2] ),
            CALCULATE (
                VALUES ( 'Table'[Visitor] ),
                FILTER ( ALL ( 'Table' ), 'Table'[Web] = MAX ( 'Table'[level2] ) )
            ),
            CALCULATE (
                VALUES ( 'Table'[Visitor] ),
                FILTER ( ALL ( 'Table' ), 'Table'[Web] = MAX ( 'Table'[level1] ) )
            )
        )
    )
)

Please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/Edz52VpyP21Fvq7ZvCowuuQBIUOZ3N8A7Hurs7L2vAeqAw?e=gxcGuw

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

Best Regards,

Dedmon Dai