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

parry2k's avatar
parry2k
Super User
6 years ago

ainsua77 you can disable the calculation in a measure using ISINSCOPE function, 

 

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡