Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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...):
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!
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:
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.
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.
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.
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
Proud to be a Super User!
There are actually a couple of culprits:
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...
User | Count |
---|---|
21 | |
19 | |
12 | |
9 | |
8 |
User | Count |
---|---|
31 | |
27 | |
15 | |
13 | |
10 |