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
