How to show different percentage returns in Matrix

[NG24]

Hi, I am having a hard time trying to show program (Hierarchy 1) returns that are not a sum of the portfolio (Hierarchy 2) returns.

I have 2 raw data tables and one is for the program and portfolio returns and the other is for sorting the portfolios into the correct order.

Portfolio	Portfolio Returns
Asia	5%
Asian Equities	2%
Asian Credit	3%
Asian Derivatives	4%

Program	Portfolio	Portfolio Sort ID
Asia	Asian Equities	1
Asia	Asian Credit	2
Asia	Asian Derivatives	3

I would like my matrix to show this (Asia shows its own returns):

Matrix	Portfolio Returns
Asia	5%
Asian Equities	2%
Asian Credit	3%
Asian Derivatives	4%

However, it is currently showing this (Asia is summing up the individual portfolio returns)

Matrix	Portfolio Returns
Asia	9%
Asian Equities	2%
Asian Credit	3%
Asian Derivatives	4%

Is there a way to show my program (Asia) return in a matrix table that doesn't sum up the individual portfolios?

Thanks

[v-jianboli-msft]

Hi @NG24 ,

Please try:

Measure = 
IF (
    ISINSCOPE ( 'Table (2)'[Portfolio] ),
    CALCULATE (
        MAX ( 'Table'[Portfolio Returns] ),
        FILTER ( 'Table', [Portfolio] = MAX ( 'Table (2)'[Portfolio] ) )
    ),
    CALCULATE (
        MAX ( 'Table'[Portfolio Returns] ),
        FILTER ( 'Table', [Portfolio] = MAX ( 'Table (2)'[Program] ) )
    )
)

Final output:

Note: There do not have any relationship between the two tables.

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[NG24]

Thanks for the measure! Your screenshot is what i'm trying to achieve without the total row.

A few followup questions:

Can this measure work if the parent does not have the highest % return in the hierarchy?

I tried testing out the measure with another hierarchies and this is what i'm seeing:

Asia has a return of 5% and Weather has a return of 0.05%

Is there a way to show the returns for Asia and Weather and remove the Total Return row?

The below are my tables with no relationships between them.

Table 1

Portfolio	Portfolio Returns
Asia	5.00%
Asian Equities	2.00%
Asian Credit	3.00%
Asian Derivatives	4.00%
Weather	0.05%
Snow	1.00%
Rain	2.00%
Ice	3.00%

Table 2

Program	Portfolio
Asia	Asian Equities
Asia	Asian Credit
Asia	Asian Derivatives
Weather	Snow
Weather	Rain
Weather	Ice

[v-jianboli-msft · Accepted solution]

Hi @NG24 ,

Please try:

Measure = 
VAR _a =
    CALCULATE (
        SUM ( 'Table 1'[Portfolio Returns] ),
        FILTER ( 'Table 1', 'Table 1'[Portfolio] = MAX ( 'Table 2'[Portfolio] ) )
    )
VAR _b =
    CALCULATE (
        SUM ( 'Table 1'[Portfolio Returns] ),
        FILTER ( 'Table 1', 'Table 1'[Portfolio] = MAX ( 'Table 2'[Program] ) )
    )
RETURN
    IF (
        HASONEVALUE ( 'Table 2'[Program] ),
        IF ( ISINSCOPE ( 'Table 2'[Portfolio] ), _a, _b ),
        BLANK ()
    )

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.