Forum Discussion
Measure that dynamically calculates average cost (avoiding circular dependency)
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:
- Perform the calculation on an Excel workbook and then integrate it back with Power BI via Power Query,.
- 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.