Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
ainsua77
Frequent Visitor

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

 

ainsua77_0-1593641976177.png

 

This is my source table:

ainsua77_1-1593642231793.png

 

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!

 

 

1 ACCEPTED SOLUTION
v-deddai1-msft
Community Support
Community Support

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] ) )
            )
        )
    )
)

 

Capture.PNG

 

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

View solution in original post

6 REPLIES 6
v-deddai1-msft
Community Support
Community Support

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] ) )
            )
        )
    )
)

 

Capture.PNG

 

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

hello I have the same issue, but in my case a child can have multiples parent:, here the output that i have :

elbardai_0-1738252937868.png

elbardai_1-1738252989375.png

 

i want something like this but i want 1 as parent have 8012 and 2 as parent have 7848 etc 

elbardai_2-1738253062467.png

 

Anonymous
Not applicable

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?

Thanks Dedmon, its works so god!!

amitchandak
Super User
Super User

@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/

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.