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

Regular Visitor

Problem with ticker filter

I've been trying for several days to calculate the measures to determine how much a company (ticker) "contributes" to the index. For example, if an index grew by 30% in a year, how much comes from each company? 4% for ABCD, 10% from XYZW and 16% from XPTO.

I have a table with the following columns:

reference_date, ticker, weight, value_variation

The daily performance of the index composed of these companies is calculated as follows:
it would be the sum(value_variation * weight) divided by the sum(weight) on that date.

daily_return_index =

VAR _average =
DIVIDE(
SUMX(
COMPOSICAO_DIARIA_INDICES,
COMPOSICAO_DIARIA_INDICES[value_variation] * COMPOSICAO_DIARIA_INDICES[weight]
),
SUMX(
COMPOSICAO_DIARIA_INDICES,
COMPOSICAO_DIARIA_INDICES[weight]
)
)
RETURN 1 + _average

The cumulative performance of this index is calculated as follows:

cumulative_return_index =
CALCULATE(
PRODUCTX(
VALUES(Calendario[Date]),
[daily_return_index]
),
FILTER(
ALLSELECTED(Calendario[Date]),
Calendario[Date] <= MAX(Calendario[Date])
)
)

What I would like to calculate is how much each ticker contributes to this cumulative performance. The formula to calculate this on a given date would be:

(cumulative performance up to that date) * (ticker's value_variation on that date * ticker's weight on that date divided by the total weight on that date) / (index performance on that date).

Then the sum of all these contributions on each date is the total contribution of that ticker.

Which I calculate as follows:

contribution_index =
CALCULATE (
SUMX (
VALUES ( Calendario[Date] ),
CALCULATE (
[cumulative_return_index],
ALLEXCEPT ( COMPOSICAO_DIARIA_INDICES, Calendario[Date] )
)
* (
CALCULATE (
SUMX (
COMPOSICAO_DIARIA_INDICES,
COMPOSICAO_DIARIA_INDICES[value_variation] * COMPOSICAO_DIARIA_INDICES[weight_index]
)
)
)
/ (
CALCULATE (
[daily_return_index],
ALLEXCEPT ( COMPOSICAO_DIARIA_INDICES, Calendario[Date] )
)
)
)
)

weight_index is this measure:

weight_index =
VAR TotalWeight = CALCULATE(SUM('COMPOSICAO_DIARIA_INDICES'[weight]), ALLEXCEPT('COMPOSICAO_DIARIA_INDICES', 'Calendario'[Date]))
RETURN DIVIDE(SUM('COMPOSICAO_DIARIA_INDICES'[weight]), TotalWeight)

What I'm doing seems to work if I don't apply any filter by ticker. But if I use a filter by ticker, the values don't change. For example, if I select only one ticker and its performance over the period was 20%, then its contribution should also be 20%, since it is the only one selected, but the calculated value is the same with all tickers

3 REPLIES 3
Super User

Hi,

Share some data to work with and show the expected result in a Table format.  Share data in a format that can be pasted in an MS Excel file.  If possible, please show the column headings in English.

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

Of course, here is an Excel file with 5 days of data:

Additionally, here is the expected result:

If it helps you, this is the Python code that I used:

Super User

Hi,

In the same_data file itself, show the formula driven result in Tab2.  I will translate those Excel formulas to DAX formulas.

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

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.