cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

# 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.

1 ACCEPTED SOLUTION
Frequent Visitor

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(
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()
)

3 REPLIES 3
Frequent Visitor

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(
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()
)

Super User

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors