Matrix hierarchy sum issue

[ainsua77]

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 as I read them but I cannot make an expression that respects the value. I need to do something like this:

This is my source table:

I resolved parent / child relationship with the functions path, pathitem, lookupvalue, etc, but I can't get the metric to just take the value of the row and not add the childs rows.

Thanks in advance!

[v-deddai1-msft · Accepted solution]

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/Edz52VpyP21Fvq7ZvC...

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]

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?

[ainsua77]

Thanks Dedmon, its works so god!!

[amitchandak]

@ainsua77 , Please find an example for calculation for isinscope mentioned by parry2k 

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

[parry2k]

@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.⚡