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.
I have a table containing a list of portfolios, their monthly returns and category.
Table 1
Portfolio | Date | Monthly Return | Category |
A | 31-Jan-24 | 1.00% | X |
A | 29-Feb-24 | 0.50% | X |
B | 31-Jan-24 | 0.20% | X |
B | 29-Feb-24 | 0.30% | X |
C | 31-Jan-24 | 2.00% | Y |
C | 29-Feb-24 | 2.50% | Y |
D | 31-Jan-24 | -0.10% | Y |
D | 29-Feb-24 | -0.80% | Y |
Using the data Table 1, I dynamically calculate the annualised return of each portfolio according to the dates selected in my date slicer with the following DAX measure:
Return_Annualised =
VAR DateSlicerStart = MIN('Table 1'[Date])
VAR DateSlicerEnd = MAX('Table 1'[Date])
VAR AnnualisationFactor = DIVIDE(DateSlicerEnd - EOMONTH(DateSlicerStart, -1), 365)
RETURN
POWER(PRODUCTX('Table 1', 1 + 'Table 1'[Monthly Return]), 1/AnnualisationFactor)-1
This gives me the following result:
From this result, how can I get the:
1) Median annualised return for each category?
2) Top quartile annualised return (if there are 4 or more data points) for each category, if less than 4 data points, return blank?
Example of desired output:
Appreciate any assistance.
Solved! Go to Solution.
Solved using the following for median:
Median_Annualised_Return =
CALCULATE(
MEDIANX(
SUMMARIZE(
'Table 1',
'Table 1'[Portfolio],
'Table 1'[Category],
"AnnualisedReturn", [Return_Annualised]
),
[AnnualisedReturn]
),
ALLEXCEPT('Table 1', 'Table 1'[Category])
)
For top quartile:
TopQuartile_Annualised_Return =
VAR AnnualisedReturns =
CALCULATETABLE(
ADDCOLUMNS(
SUMMARIZE(
'Table 1',
'Table 1'[Portfolio],
),
"AnnualisedReturn",[Return_Annualised]
),
ALLEXCEPT('Table 1', 'Table 1'[Category])
)
VAR ReturnCount = COUNTROWS(AnnualisedReturns)
VAR TopQuartileReturn =
IF(
ReturnCount >=4,
PERCENTILEX.EXC(AnnualisedReturns, [AnnualisedReturn], 0.75),
BLANK()
)
RETURN TopQuartileReturn
Solved using the following for median:
Median_Annualised_Return =
CALCULATE(
MEDIANX(
SUMMARIZE(
'Table 1',
'Table 1'[Portfolio],
'Table 1'[Category],
"AnnualisedReturn", [Return_Annualised]
),
[AnnualisedReturn]
),
ALLEXCEPT('Table 1', 'Table 1'[Category])
)
For top quartile:
TopQuartile_Annualised_Return =
VAR AnnualisedReturns =
CALCULATETABLE(
ADDCOLUMNS(
SUMMARIZE(
'Table 1',
'Table 1'[Portfolio],
),
"AnnualisedReturn",[Return_Annualised]
),
ALLEXCEPT('Table 1', 'Table 1'[Category])
)
VAR ReturnCount = COUNTROWS(AnnualisedReturns)
VAR TopQuartileReturn =
IF(
ReturnCount >=4,
PERCENTILEX.EXC(AnnualisedReturns, [AnnualisedReturn], 0.75),
BLANK()
)
RETURN TopQuartileReturn
Hi,
Share the download link of the PBI file.
Hi @Ashish_Mathur I would be more than happy to share the file if there was an option to attach it to the post directly. If any of you folks at Microsoft are reading this it would be great if you could add this feature.
User | Count |
---|---|
79 | |
74 | |
62 | |
61 | |
45 |
User | Count |
---|---|
108 | |
98 | |
89 | |
82 | |
61 |