Forum Discussion

Ccollet's avatar
Ccollet
Regular Visitor
1 year ago
Solved

Calculating Average Acquisition Price in Power BI – Circular Formula Issue

Hello, I would like to calculate the average acquisition price of my stock positions, similar to what banking applications do. To achieve this, I am trying to adapt the mathematical formula to Powe...
Need Help
  • Ccollet's avatar
    Ccollet
    1 year ago

    Thank you for your proposal, it helped me work around the circular reference error in my original formula. I’ve since adjusted it to better fit my needs and incorporated additional elements, such as transaction taxes on sales and rewards (staking interest on cryptocurrencies). I also revisited the formula from a mathematical perspective, as there was an issue with consecutive sales where the price wasn’t adjusting correctly. Below are the final formulas I’ve designed:

     

    ID transaction =
    RANKX(
        ALLSELECTED('1.0_Tout'),
        VALUE('1.0_Tout'[Transaction Date])&'1.0_Tout'[Source]&'1.0_Tout'[Transaction Type]&'1.0_Tout'[Company Name],
        ,
        ASC,
        DENSE
    )
     
    Amount1 =
    IF(
        '1.0_Tout'[Transaction Type] IN {"Achat Comptant", "TAXE TRANSAC FINAN"},
        [Transaction Amount]
    )

     

    PeriodID =
    VAR _currentID = '1.0_Tout'[ID transaction]
    VAR _previousTransactions =
        FILTER(
            '1.0_Tout',
            '1.0_Tout'[ID transaction] < _currentID &&
            '1.0_Tout'[Company Name] = EARLIER('1.0_Tout'[Company Name]) &&
            '1.0_Tout'[Source] = EARLIER('1.0_Tout'[Source])
        )
    VAR _totalQuantity = SUMX(_previousTransactions, '1.0_Tout'[Transaction Quantity])
    RETURN
        IF(
            _totalQuantity = 0,
            1,
            0
        )

     

    CumulativePeriodID =
    CALCULATE(
        SUM('1.0_Tout'[PeriodID]),
        FILTER(
            '1.0_Tout',
            '1.0_Tout'[ID transaction] <= EARLIER('1.0_Tout'[ID transaction]) &&
            '1.0_Tout'[Company Name] = EARLIER('1.0_Tout'[Company Name]) &&
            '1.0_Tout'[Source] = EARLIER('1.0_Tout'[Source])
        )
    )

     

    Amount2 =
    VAR _quantiteVendue = ABS('1.0_Tout'[Transaction Quantity])
    VAR _vente = '1.0_Tout'[Transaction Type] = "Vente comptant"

     
    VAR _historiqueAchats =
        FILTER(
            '1.0_Tout',
            '1.0_Tout'[ID transaction] < EARLIER('1.0_Tout'[ID transaction]) &&
            '1.0_Tout'[Company Name] = EARLIER('1.0_Tout'[Company Name]) &&
            '1.0_Tout'[Source] = EARLIER('1.0_Tout'[Source]) &&
            '1.0_Tout'[CumulativePeriodID] = EARLIER('1.0_Tout'[CumulativePeriodID]) &&
            '1.0_Tout'[Transaction Type] IN {"Achat Comptant", "TAXE TRANSAC FINAN"}
        )

    VAR _montantTotalAchats = SUMX(_historiqueAchats, '1.0_Tout'[Amount1])
    VAR _quantiteTotaleAchats = SUMX(_historiqueAchats, '1.0_Tout'[Transaction Quantity])

    VAR _fraisVentes =
        IF(
            _vente,
            SUMX(
                FILTER(
                    '1.0_Tout',
                    '1.0_Tout'[ID transaction] < EARLIER('1.0_Tout'[ID transaction]) &&
                    '1.0_Tout'[Company Name] = EARLIER('1.0_Tout'[Company Name]) &&
                    '1.0_Tout'[Source] = EARLIER('1.0_Tout'[Source]) &&
                    '1.0_Tout'[CumulativePeriodID] = EARLIER('1.0_Tout'[CumulativePeriodID]) &&
                    '1.0_Tout'[Transaction Type] = "Vente comptant"
                ),
                '1.0_Tout'[Frais courtage]
            ),
            0
        )

    VAR _wac = IF(_quantiteTotaleAchats > 0, (_montantTotalAchats + _fraisVentes) / _quantiteTotaleAchats, BLANK())

     
    VAR _final =
        IF(
            _vente,
            _wac * _quantiteVendue * (-1),
            [Amount1]
        )

    RETURN _final

     

    Amount =
    IF(
        '1.0_Tout'[Transaction Type] IN {"Achat Comptant", "Vente comptant", "Reward","TAXE TRANSAC FINAN"},
        CALCULATE(
            SUM('1.0_Tout'[Amount2]),
            FILTER(
                '1.0_Tout',
                '1.0_Tout'[ID transaction] <= EARLIER('1.0_Tout'[ID transaction]) &&
                '1.0_Tout'[Company Name] = EARLIER('1.0_Tout'[Company Name]) &&
                '1.0_Tout'[Source] = EARLIER('1.0_Tout'[Source])
            )
        )

    )

     

    FinalWAC = SWITCH(
        '1.0_Tout'[Transaction Type],
        "Achat Comptant", -[Amount] / '1.0_Tout'[Cumulative Quantity],
        "Reward", -[Amount] / '1.0_Tout'[Cumulative Quantity],
        "TAXE TRANSAC FINAN", -[Amount] / '1.0_Tout'[Cumulative Quantity]
    )

     

    I still have a small difference on cryptocurency price but i should find why 🙂

     

    A huge thanks to all it really help me on something that I thought impossible !

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Thanks for the reply from bhanu_gautam.

 

Hi Ccollet ,

 

Your formula for calculating WAC requires the previous WAC value, leading to a circular reference. I tried to create a sample data myself and realize the result according to your request.

 

Please check if it can be improved. This is my solution by creating a calculated column:

WAC = 
VAR _Quantity1 =
    SUMX (
        FILTER (
            ALL ( '1.0_Tout' ),
            '1.0_Tout'[Transaction Date] <= EARLIER ( '1.0_Tout'[Transaction Date] )
        ),
        IF (
            '1.0_Tout'[Transaction Type] = "BUY",
            '1.0_Tout'[Transaction Quantity],
            - '1.0_Tout'[Transaction Quantity]
        )
    )
VAR _Quantity2 =
    SUMX (
        FILTER (
            ALL ( '1.0_Tout' ),
            '1.0_Tout'[Transaction Date] <= EARLIER ( '1.0_Tout'[Transaction Date] )
        ),
        IF ( '1.0_Tout'[Transaction Type] = "BUY", '1.0_Tout'[Transaction Quantity], 0 )
    )
VAR _Amount1 =
    SUMX (
        FILTER (
            ALL ( '1.0_Tout' ),
            '1.0_Tout'[Transaction Date] <= EARLIER ( '1.0_Tout'[Transaction Date] )
        ),
        IF (
            '1.0_Tout'[Transaction Type] = "BUY",
            '1.0_Tout'[Transaction Amount],
            - '1.0_Tout'[Transaction Amount]
        )
    )
VAR _Amount2 =
    SUMX (
        FILTER (
            ALL ( '1.0_Tout' ),
            '1.0_Tout'[Transaction Date] <= EARLIER ( '1.0_Tout'[Transaction Date] )
        ),
        IF ( '1.0_Tout'[Transaction Type] = "BUY", '1.0_Tout'[Transaction Amount], 0 )
    )
RETURN
    SWITCH (
        '1.0_Tout'[Transaction Type],
        "BUY", IF ( _Quantity1 > 0, DIVIDE ( _Amount1, _Quantity1 ), 0 ),
        "SELL", IF ( _Quantity2 > 0, DIVIDE ( _Amount2, _Quantity2 ), 0 )
    )

 

Result:

 

Best Regards,
Zhu

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

  • Ccollet's avatar
    Ccollet
    Regular Visitor
    1 year ago

    Unfortunately this solution dosen't get me the good resolts event went I adapted it by adding filters for my database 😢





    WAC =
    VAR _Quantity1 =
        SUMX (
            FILTER (
                ALL ( '1.0_Tout' ),
                '1.0_Tout'[ID transaction] <= EARLIER ( '1.0_Tout'[ID transaction] )
                && '1.0_Tout'[Company Name] = EARLIER ( '1.0_Tout'[Company Name] )
                && '1.0_Tout'[Source] = EARLIER ( '1.0_Tout'[Source] )
            ),
            IF (
                '1.0_Tout'[Transaction Type] = "Achat Comptant",
                '1.0_Tout'[Transaction Quantity],
                - '1.0_Tout'[Transaction Quantity]
            )
        )
    VAR _Quantity2 =
        SUMX (
            FILTER (
                ALL ( '1.0_Tout' ),
                '1.0_Tout'[ID transaction] <= EARLIER ( '1.0_Tout'[ID transaction] )
                && '1.0_Tout'[Company Name] = EARLIER ( '1.0_Tout'[Company Name] )
                && '1.0_Tout'[Source] = EARLIER ( '1.0_Tout'[Source] )
            ),
            IF ( '1.0_Tout'[Transaction Type] = "Achat Comptant", '1.0_Tout'[Transaction Quantity], 0 )
        )
    VAR _Amount1 =
        SUMX (
            FILTER (
                ALL ( '1.0_Tout' ),
                '1.0_Tout'[ID transaction] <= EARLIER ( '1.0_Tout'[ID transaction] )
                && '1.0_Tout'[Company Name] = EARLIER ( '1.0_Tout'[Company Name] )
                && '1.0_Tout'[Source] = EARLIER ( '1.0_Tout'[Source] )
            ),
            IF (
                '1.0_Tout'[Transaction Type] = "Achat Comptant",
                '1.0_Tout'[Transaction Amount],
                - '1.0_Tout'[Transaction Amount]
            )
        )
    VAR _Amount2 =
        SUMX (
            FILTER (
                ALL ( '1.0_Tout' ),
                '1.0_Tout'[ID transaction] <= EARLIER ( '1.0_Tout'[ID transaction] )
                && '1.0_Tout'[Company Name] = EARLIER ( '1.0_Tout'[Company Name] )
                && '1.0_Tout'[Source] = EARLIER ( '1.0_Tout'[Source] )
            ),
            IF ( '1.0_Tout'[Transaction Type] = "Achat Comptant", '1.0_Tout'[Transaction Amount], 0 )
        )
    RETURN
        SWITCH (
            '1.0_Tout'[Transaction Type],
            "Achat Comptant", IF ( _Quantity1 > 0, DIVIDE ( _Amount1, _Quantity1 ), 0 ),
            "Vente Comptant", IF ( _Quantity2 > 0, DIVIDE ( _Amount2, _Quantity2 ), 0 )
        )