Forum Discussion

ainsua77's avatar
ainsua77
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's avatar
    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's avatar
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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Dedmon, the link you shared for pbix file does not work. Can you please share it again? or can you please explain what level1, level2,..etc columns hold?