Finding the median and top quartile of a category based on figures from another DAX measure

[spart_1337]

Context

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:

My Question

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.

[spart_1337 · Accepted 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

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[spart_1337]

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.