Forum Discussion

fsfs's avatar
fsfs
Helper I
5 years ago
Solved

problem while multiplying column by measure (doesn't work)

Hello champs!

 

To give you initial context:

- I'm working on stock exchange data

- the dim table contains purchases (ticker, amount, price, date, currency, etc)

- the fact table contains all prices of all tickers from dim table from the moment they were bought, plus dividends and splits

- dividends and split data are not continous (these have just 0, unless split/dividend happens)

- split works as follows - if one had 40 stocks and the split was 0.25, the amount of stocks is multiplied by 0.25 and the price is divided by 0.25, its a EV=0 situation, in practice - if the split is tomorrow:

I have 40 stocks today, tomorrow 10 but they are worth the same in total (the price rises automatically x4)

 

the problem:

- for the sake of calculating the dividends (its paid per stock) I need to know how much of stocks the investor held at a given moment in time, so I need a split-adjusted state of portfolio for any given day

 

solution I tried:

- I've created a set of measures to have a RUNNING CURRENT AMOUNT with the codes:

 

Current Split Multiplier =
CALCULATE(
CALCULATE( SUMX(
'stocks-history'
, 'stocks-history'[Stock Splits]
) )
, FILTER(
ALLSELECTED('stocks-history'[_Date])
, 'stocks-history'[_Date] <= MAX('stocks-history'[_Date])
)
)

Current Amount (for dividends) =
SUMX(
investments
, investments[Purchase Amount] * IF( [Current Split Multiplier] = 0, 1, [Current Split Multiplier] ) )

 

when I put it on matrix, along with dates it looks all good (split is on 23.06.20):

but when I'm trying to create a measure to calculate dividend value:

internal - Dividends =
SUMX(
'stocks-history'
, [Current Amount (for dividends)] * 'stocks-history'[Dividends] )

 

it ignores the splits and uses the full investments[Purchase Amount]:

 

I think it is something fundamental and goes along with additional context, probably coming from the IF statement returning static value?, but as a beginner I couldn't figure it out so far

any ideas 🙂 ?

 
 
 
  • fsfs's avatar
    fsfs
    5 years ago

    I think I might have figured it out

     

    the problem that I had seemed to be because I was mixing the facts (hardcoded data in columns) with the measures in a one measure, in which case the measure tend to have a single value (like with VAR statement)

     

    I fixed it with turning the facts into measures with
    ( CALCULATE ( SUMX ( table, column_that_i_needed_elsewhere_as_measure), FILTER( calendar_table, calendar_table[date] = MAX(calendar_table[date]) ) ) )

    and it seems to work as intended

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Let's get the basics out of the way first.

     

    "the dim table contains purchases (ticker, amount, price, date, currency, etc)" 

     

    This table is not a dimension table. It's a fact table as well. Dimensions are attributes of the processes you capture. Here you capture Purchases (clearly a process). A dimesion would be: Ticker, Currency, Date... These are dimensions. Fact tables must only store keys to dimensions and figures (statistics) about the process.

     

    As for the calculations... We need data (representative of the problem) to work with in text form. Or a link to a file with data. Then we'll most likely be able to help.

     

    Thanks.

    • fsfs's avatar
      fsfs
      Helper I
      5 years ago

      I'm attaching sample data for both FACT tables (thanks for clarifying 🙂 )

       

      https://drive.google.com/file/d/15QvrFRh3c4T5ANTIQIksHxYJkTRIpcE2/view?usp=sharing

      https://drive.google.com/file/d/1rp_Q37ay5asEg8xinvqF9sJSc58FwCNN/view?usp=sharing

       

      just additional context:

      - in the attached data the investor hold 10000 shares of 0017.hk until 22.06.2020 (id. 474)

      - until then, he is given 10000 x dividend (id. 12 ,id. 260, id. 378)

      - from 23.06.2020 (id. 475) the investor hold 2500 shares of 0017.hk and from that moment on this is the base for the dividend amount

      - in the whole dataset the prices are adjusted to the splits and dividends backwards (which means that in the reality, the stock price on 22.06.2020 was around 9 HKD, but the data is altered by the data vendor to resemble continuity) - if there was another split tomorrow, lets say 5:1, if I connect to the server tomorrow, all the prices will be divided by 5

       

       

       

      now Im thinking about having another fact table with the current state of portfolio, but that seems to lack elegancy 

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Thanks. I'm going to look at this soon. But I suspect there'll be some back-and-forth since I'll have to acquire some good understanding of the nature of the data. I have a background in financial engineering (which certainly helps) but have not worked in the field for a while.

  • fsfs's avatar
    fsfs
    Helper I
    5 years ago

    I think I might have figured it out

     

    the problem that I had seemed to be because I was mixing the facts (hardcoded data in columns) with the measures in a one measure, in which case the measure tend to have a single value (like with VAR statement)

     

    I fixed it with turning the facts into measures with
    ( CALCULATE ( SUMX ( table, column_that_i_needed_elsewhere_as_measure), FILTER( calendar_table, calendar_table[date] = MAX(calendar_table[date]) ) ) )

    and it seems to work as intended