Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to 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.
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.
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 |
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.
User | Count |
---|---|
84 | |
84 | |
68 | |
62 | |
56 |
User | Count |
---|---|
137 | |
110 | |
92 | |
84 | |
69 |