Skip to main content
cancel
Showing results for 
Search instead 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

Reply
leolapa_br
Resolver I
Resolver I

Measure that dynamically calculates average cost (avoiding circular dependency)

I imported an Excel table with thousands of purchases and sales transactions of shares of stock.

 

The imported data: traded tickers, transaction dates, number of traded shares, types of trade (purchase or sale), and traded share prices.

 

I then calculated a measure that manages to tally the running Shares balance after each purchase/sale transaction occurs under each row of data.

 

3 other measures are also easily calculated:

- Purchase total: Traded (purchased) shares x Share price
- Total revenue: Traded (sold) shares x Share price
- Total cost: Traded (sold) shares x Average cost

 

I then need to calculate the last measure being Average cost, and that's where I get stuck with a circular dependency issue...

 

The rationale behind this calculation is as follows:

- If the transaction is a sale, just pick up the prior Average cost from the same column.

- But if the transaction is a purchase, then [ALL Purchase totals up to the respective date for the respective ticker + ALL Total cost (negative) BEFORE the respective date for the respective ticker] DIVIDED BY the shares balance at the respective date.

 

The below screenshot tries to illustrate everything described above (I hope it doesn't end up providing a confusing picture...):

Avg_Cost_Sample.png

 

Since the actual code is pretty lengthy I'll split it up into 4 sections to make it easier to comprehend.

 

1) The prior Average cost picked up from the same column is computed by the following code:

 

Average cost = 
VAR Avg_Cost_Tbl = 
    CALCULATETABLE( 
        ADDCOLUMNS( 
            SUMMARIZE( 
                fTrans, 
                fTrans[Ticker], 
                fTrans[Date] 
            ), 
            "@AvgCost", [Average cost]
        ), 
        fTrans[Ticker] = fTrans[Ticker], 
        fTrans[Date] <= fTrans[Date] 
    )
VAR Prior_Avg_Cost = 
	FILTER( 
		Avg_Cost_Tbl, 
		SELECTCOLUMNS( 
			OFFSET( 
				-1, 
				Avg_Cost_Tbl, 
				ORDERBY( fTrans[Ticker], ASC, fTrans[Date], ASC ) 
			), 
			[@AvgCost] 
		)
	)

 

2) ALL Purchase totals up to the respective date for the respective ticker is calculated by the following code:

 

VAR Purch_Total_Tbl =
	CALCULATETABLE( 
		ADDCOLUMNS( 
			SUMMARIZE( 
				fTrans, 
				fTrans[Ticker], 
				fTrans[Date], 
				fTrans[Transaction] 
			), 
			"@PurchTotal", [Purchase total]
		), 
		fTrans[Ticker] = fTrans[Ticker], 
		fTrans[Date] <= fTrans[Date], 
		fTrans[Transaction] = "Purchase"
	)
VAR Purch_Total_Sum = 
    IF( 
        HASONEVALUE( fTrans[Ticker] ), 
        SUMX( 
            WINDOW( 
                1, 
                ABS, 
                0, 
                REL, 
                Purch_Total_Tbl, 
                ORDERBY( fTrans[Date], ASC ), 
                , 
                PARTITIONBY( fTrans[Ticker] ) 
            ), 
            [@PurchTotal] 
        ) 
    )

 

3) ALL Total cost PRIOR to the respective date for the respective ticker is calculated by the following code:

 

VAR Total_Cost_Tbl =
	CALCULATETABLE( 
		ADDCOLUMNS( 
			SUMMARIZE( 
				fTrans, 
				fTrans[Ticker], 
				fTrans[Date], 
				fTrans[Transaction] 
			), 
			"@Total_Cost", [Total cost]
		), 
		fTrans[Ticker] = fTrans[Ticker], 
		fTrans[Date] <= fTrans[Date], 
		fTrans[Transaction] = "Sale"
	)
VAR Total_Cost_Sum = 
    IF( 
        HASONEVALUE( fTrans[Ticker] ), 
        SUMX( 
            WINDOW( 
                1, 
                ABS, 
                -1, 
                REL, 
                Total_Cost_Tbl, 
                ORDERBY( fTrans[Date], ASC ), 
                , 
                PARTITIONBY( fTrans[Ticker] ) 
            ), 
            [@Total_Cost] 
        ) 
    )

 

4) Finally, the above 3 pieces of code come together via the following code:

 

RETURN
    SUMX( 
        fTrans, 
        IF( 
            fTrans[Transaction] = "Sale", 
            Purch_Total_Sum, 
            DIVIDE( 
                Purch_Total_Sum + Total_Cost_Sum, 
                [Saldo de cotas] 
            ) 
        ) 
    )

 

 

 

But then I keep getting the circular dependency error message, which I tried to avoid by making sure the code picks up dependent data from PRIOR rows, albeit I fail on attempting to do so.

 

How can I get this code fixed to achieve that? Or should a completely different calculation approach be taken?

 

I suppose calculated columns could be a way to achieve that but I'd like to avoid that route as we're talking about a table with tens of thousands of rows of data.

 

Thanks in advance for any help!

6 REPLIES 6
daniel_gatti2
New Member

Hi leolapa_br.

Did you find the solution for the circular dependency?

I am having this exact problem too(custo medio rsrs). Been trying some stuff for a couple days, but nothing worked.

 

Hi @daniel_gatti2, after quite some research I learned/realized that the most granular piece of data for DAX is a column, not a cell as it is with Excel.

 

With that said, it ends up being impossible through DAX to achieve such average cost calculation by referring back to the latest data out of the same column itself, just as we can do when performing the same calculation on Excel.

So I ended up with two options:

  1. Perform the calculation on an Excel workbook and then integrate it back with Power BI via Power Query,.
  2. Deconstruct, or sort of "reverse engineer" the entire calculation process in order to find a way to bypass the need to refer back to prior data from the same column, and then come up with another calculation method that would make me achieve the same goal, but not referring back to the column itself.

I ended up going with option 2 by dumping part of the inventory movement historical data to an Excel sheet and then breaking down the average cost calculation method step by step until I came up with an alternative way to do so (this Excel sheet served only as a sandbox tool for me to get where I wanted). I then applied the same step-by-step methodology to calculated columns in Power BI and was then able to get to the same goal while not having to refer back to the same column and therefore avoiding any potential circular dependency issues.

some_bih
Super User
Super User

Hi @leolapa_br DAX is "specific" for row by row calculation, when we compare it with Excel. To perform some "standard" calculation in Excel to implement and leverage in DAX a lot of steps should be performed, this is my experience. I suggest you to analyse if work could be dan column by column approach in DAX, this should be base to implement Excel best practice how to deal with your scenario. 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






Thanks foy your valuable input @some_bih.

 

I also posted the same question on Stack Overflow forum and an user also suggested the same approach you're suggesting by using calculated columns to do some prior calculations before getting to the point of writing the measure code.

 

She even went as far as providing the code for the required columns but I still ran into circular dependency while trying to implement her approach. I notified her of the issue and let's see if she'll reply with some fix.

some_bih
Super User
Super User

Hi @leolapa_br did you spot which part of your code create circular dep. issue? I would suggest to debug part by part starting it with first, second VAR-iable ... continue to spot issue. This approach should be time saver in case you want / need another logic to create. Hope this help





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






There are actually a couple of culprits:

  1. The prior average cost piece of the code (part 1 of my question above), as it's attempting to get the prior row from the very same column.
  2. The total cost of shares sold PRIOR to the date of each respective row (part 3 of my question above) since it's trying to sum up one calculated item that uses average cost itself as part of its own calculation.

I suspected I was going to run into this circular dependency issue right when I started developing the code since the pieces are interdependent upon themselves. However, the method is careful enough to go after rows of data that although from precedent data, happened prior to each respective row where the calculation is taking place, thus a circular dependency issue should be avoided.

 

This same dynamic calculation technique is applied in Excel using OFFSET and/or INDEX functions that makes the calculation ranges sort of 'run along' prior to each row of data, so when well implemented they don't bump into circular referencing issues. So I thought we're supposed to accomplish the same in DAX, I just don't know how...

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Users online (1,708)