Power BI Matrix Last Node (in Rows) should display on all levels (of rows) as and when expanded

[Alpha0904]

Dear Community, Is the following requirment feasible in power bi matrix ? client wants to see the last most node in the rows to appear at all level as and when expanded and collapsed. Illustrated below

[Alpha0904]

Hi @rohit1991 , Thanks for sharing... each of my power bi matrix has about 60+ measures and each matrix has different set of combination of measures.. not sure if its still manageable your route ? can you share a sample pbix file on the lines of your proposal , since above is little difficult for me to follow along 

[v-sshirivolu]

Hi @Alpha0904 ,
I was able to reproduce your scenario and have shared a sample PBIX for reference where the matrix is structured with Category and Product in rows and Subcategory in columns and the requirement of showing the last leaf level value at higher levels instead of aggregated totals is working as expected you will notice that at the Category level the values reflect the last product within each subcategory rather than a sum which should align with your requirement even in more complex setups so please have a look at the file.
This approach should still be manageable even with multiple measures and can be further optimized if needed.

Please find the below attached .pbix file.
Thank you.

[Alpha0904]

Dear @v-sshirivolu , Thanks for sharing however i looked at it and not sure how this will fulfill my reqruiement. I want The Types to be shown at all levels when they are opened up... i guess you are proposing to shift it to columns rather ?

[v-sshirivolu · Accepted solution]

Hi @Alpha0904 ,
Thanks for checking and for the clarification, as u said my previous example used columns mainly to demonstrate the value behavior but that is not required for your scenario, from what you described you want the last level Types to be visible across all levels in the row hierarchy when expanded, and this is more about how the matrix displays hierarchy than just measure logic because Power BI does not natively repeat leaf level values at higher levels, the PBIX I shared was focused on value behavior so it may not fully match your requirement, achieving this would need some additional modeling or DAX approach rather than just visual settings, if you can share a small sample of your hierarchy It would be helpful.

[v-sshirivolu]

Hi @Alpha0904 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

[v-sshirivolu]

Hi @Alpha0904 ,

I would also take a moment to thank @rohit1991  , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions

[rohit1991]

Hii @Alpha0904 

It can be achieved using a DAX measure that identifies the current hierarchy level and dynamically returns the last child node’s value for parent levels.

The approach uses ISINSCOPE to detect the level and CALCULATE with MAX to fetch the last node value based on a properly sorted column.

Create Last Node Measure:-

Last Node Value =
CALCULATE(
    MAX('Table'[Value]),
    FILTER(
        ALL('Table'),
        'Table'[Type] = 
            MAXX(
                FILTER(
                    ALL('Table'),
                    'Table'[Row2] = MAX('Table'[Row2])
                ),
                'Table'[Type]
            )
    )
)

Show at All Levels:-

Final Value =
VAR IsLowestLevel = ISINSCOPE('Table'[Type])

RETURN
IF(
    IsLowestLevel,
    SELECTEDVALUE('Table'[Value]),
    [Last Node Value]
)

To correctly identify the “last” node, define a custom sort order:-

SortOrder = 
SWITCH(
    'Table'[Type],
    "Type1",1,
    "Type2",2,
    "Type3",3
)