Calculating Average Acquisition Price in Power BI – Circular Formula Issue

[Ccollet]

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 Power BI, but without success:

If buying:

(Current quantity owned × Average acquisition price) + (Quantity purchased × Acquisition price )

Divided by Total quantity = (Current quantity owned + Quantity purchased)​

If selling:
The average acquisition price remains unchanged, but the quantity owned decreases, which will affect the next purchase.

However, I am struggling to find a solution in Power BI because I frequently encounter circular formula issues. My formula retrieves the last acquisition price calculated by the same formula, which Power BI does not allow due to circular references.

Otherwise, I end up with a formula that only calculates my average purchase price but does not take sales into account, which distorts my result.

My data table has the following columns:

[Company Name] that is buyed or selled

[Transaction Date]

[Transaction Quantity]

[Transaction Amount]

[Transaction Type] (Sell or Buy)

[Transaction ID]

My progress idea

Current formula:
WAC =
IF('1.0_Tout'[Operation] = "BUY",
SWITCH(
TRUE(),
'1.0_Tout'[Cumulative Quantity -1] = 0,
-'1.0_Tout'[Net Amount] / '1.0_Tout'[Quantity],

'1.0_Tout'[Cumulative Quantity -1] <> 0,
('1.0_Tout'[Cumulative Quantity -1] * [WAC] - '1.0_Tout'[Net Amount]) /
'1.0_Tout'[Cumulative Quantity]
)
)

[Ccollet]

Hello thanks for your response, I tryed your both response unfortunately they don't work or dont make the result I expect.

Her is my power bi file maybe it can help you :

Power BI File 

This is what i'm trying to get, the results like the GOAL colomn, i'm abble to create a formula in excel that works but can't do anything in power bi.

[Anonymous]

Hi @Ccollet ,

Please create the following calculated columns:

Amount1 = IF('1.0_Tout'[Transaction Type]="Achat Comptant",[Transaction Amount])

Amount2 = 
VAR _maxdate=MAXX(FILTER('1.0_Tout','1.0_Tout'[Date]<EARLIER('1.0_Tout'[Date])&&'1.0_Tout'[Company Name]=EARLIER('1.0_Tout'[Company Name])),'1.0_Tout'[Date])
var _final=SWITCH('1.0_Tout'[Transaction Type],"Achat Comptant",[Amount1],"Vente comptant",CALCULATE(SUM('1.0_Tout'[Amount1]),FILTER('1.0_Tout','1.0_Tout'[Date]=_maxdate&&'1.0_Tout'[Company Name]=EARLIER('1.0_Tout'[Company Name])))*(-1))
RETURN
_final

Amount = IF('1.0_Tout'[Transaction Type] IN {"Achat Comptant","Vente comptant"},CALCULATE(SUM('1.0_Tout'[Amount2]),FILTER('1.0_Tout','1.0_Tout'[Date]<=EARLIER('1.0_Tout'[Date])&&'1.0_Tout'[Company Name]=EARLIER('1.0_Tout'[Company Name]))))

FinalWAC = SWITCH('1.0_Tout'[Transaction Type],"Achat Comptant",-[Amount]/'1.0_Tout'[Cumulative Quantity],"Vente comptant",[Amount2])

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 · Accepted solution]

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]

Hi @Ccollet ,

It's great to see that you solved the problem through your own efforts. Could you mark your reply as the solution? This will also help others with similar problems. Thank you very much for your contribution!

Best Regards,
Zhu

[Anonymous]

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]

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 )

    )

[bhanu_gautam]

@Ccollet Create a Calculated Column for Cumulative Quantity:

DAX
Cumulative Quantity =
CALCULATE(
SUM('1.0_Tout'[Transaction Quantity]),
FILTER(
'1.0_Tout',
'1.0_Tout'[Company Name] = EARLIER('1.0_Tout'[Company Name]) &&
'1.0_Tout'[Transaction Date] <= EARLIER('1.0_Tout'[Transaction Date]) &&
'1.0_Tout'[Transaction ID] <= EARLIER('1.0_Tout'[Transaction ID])
)
)

Create a Calculated Column for Cumulative Amount:

DAX
Cumulative Amount =
CALCULATE(
SUM('1.0_Tout'[Transaction Amount]),
FILTER(
'1.0_Tout',
'1.0_Tout'[Company Name] = EARLIER('1.0_Tout'[Company Name]) &&
'1.0_Tout'[Transaction Date] <= EARLIER('1.0_Tout'[Transaction Date]) &&
'1.0_Tout'[Transaction ID] <= EARLIER('1.0_Tout'[Transaction ID])
)
)

Create a Calculated Column for Average Acquisition Price

DAX
Average Acquisition Price =
IF(
'1.0_Tout'[Cumulative Quantity] <> 0,
'1.0_Tout'[Cumulative Amount] / '1.0_Tout'[Cumulative Quantity],
BLANK()
)

Create a Measure for the Current Average Acquisition Price:

DAX
Current Average Acquisition Price =
CALCULATE(
LASTNONBLANK('1.0_Tout'[Average Acquisition Price], '1.0_Tout'[Transaction Date]),
FILTER(
'1.0_Tout',
'1.0_Tout'[Company Name] = MAX('1.0_Tout'[Company Name])
)
)

Cumulative Quantity =
CALCULATE(
SUMX(
FILTER(
'1.0_Tout',
'1.0_Tout'[Company Name] = EARLIER('1.0_Tout'[Company Name]) &&
'1.0_Tout'[Transaction Date] <= EARLIER('1.0_Tout'[Transaction Date]) &&
'1.0_Tout'[Transaction ID] <= EARLIER('1.0_Tout'[Transaction ID])
),
IF('1.0_Tout'[Transaction Type] = "BUY", '1.0_Tout'[Transaction Quantity], -'1.0_Tout'[Transaction Quantity])
)
)

Cumulative Amount =
CALCULATE(
SUMX(
FILTER(
'1.0_Tout',
'1.0_Tout'[Company Name] = EARLIER('1.0_Tout'[Company Name]) &&
'1.0_Tout'[Transaction Date] <= EARLIER('1.0_Tout'[Transaction Date]) &&
'1.0_Tout'[Transaction ID] <= EARLIER('1.0_Tout'[Transaction ID])
),
IF('1.0_Tout'[Transaction Type] = "BUY", '1.0_Tout'[Transaction Amount], 0)
)
)