Forum Discussion
Circular Dependency in Power Bi calculated column
Hi lbendlin
We will also require Weighted Average NAV and daily changes column for further calculations.
NAV is value of each unit of investment. I am calculating weighted average NAV by dividing (Closing Investment) by (Closing no. of units).
Super UserI get rather different numbers:
"NAV cumul" shows the NAV of the remaining units after each transaction.
Hi lbendlin
For your convenience, I've written the formulas in each column. I am encountering a circular dependency while calculating "Changes in closing investments" for "02/04/2023". Since the formula for calculating "Changes in closing investments" for "02/04/2023" is "NOUW Direction * weighted average cost of 01/04/2023," and weighted average cost of 01/04/2023 is determined by dividing "Closing Investment by Closing Units of 01/04/2023,"
Regards and thanks
Durgesh Gupta
*NOUW = Number of Units With
Company Code Transaction Date ID_number Flow Direction_4 Purchase/ Sales Value No. of units Price per unit Rank NOUW Direction Closing units Changes in Closing Investment Closing Investment Weighted Average Cost Previous Day Weighted Avg Cost Cost of investment sold Profit ROI Formula 1= Purchase
-1 = SalesNo of units purchased or sold No of units × Direction Running total of "NOUW Direction" In case of :-
Purchase = Purchase/ Sales value
Sales = Previous day weighted avg cost" × NOUW DirectionRunning Total of "Changes in closing investment" Divison of "Closing investment" by "Closing units" Previous day "Weighted average cost" "Previous day weighted avg cost" × "NOUW Direction" "Purchase/Sales Value" - "Cost of investment sold" Division of "Profit" by "Cost of investment sold" 20001 1 01/04/2023 ADITYABMF Purchase 1 100,000.00 1000 100.00 1 1000 1000 100,000.00 100,000.00 100.00 - - - 0.00% 20001 2 02/04/2023 ADITYABMF Sale -1 22,000.00 200 110.00 2 -200 800 (20,000.00) 80,000.00 100.00 100.00 (20,000.00) 2,000.00 10.00% 20001 3 03/04/2023 ADITYABMF Purchase 1 31,500.00 300 105.00 3 300 1100 31,500.00 111,500.00 101.36 100.00 - - 0.00% 20001 4 04/04/2023 ADITYABMF Sale -1 10,600.00 100 106.00 4 -100 1000 (10,136.36) 101,363.64 101.36 101.36 (10,136.36) 463.64 4.57% 20001 5 05/04/2023 ADITYABMF Sale -1 32,400.00 300 108.00 5 -300 700 (30,409.09) 70,954.55 101.36 101.36 (30,409.09) 1,990.91 6.55% 20001 6 06/04/2023 ADITYABMF Purchase 1 30,900.00 300 103.00 6 300 1000 30,900.00 101,854.55 101.85 101.36 - - 0.00% 20001 7 07/04/2023 ADITYABMF Purchase 1 26,000.00 250 104.00 7 250 1250 26,000.00 127,854.55 102.28 101.85 - - 0.00% 20001 8 08/04/2023 ADITYABMF Sale -1 75,600.00 700 108.00 8 -700 550 (71,598.55) 56,256.00 102.28 102.28 (71,598.55) 4,001.45 5.59% (132,144.00) 8,456.00 6.40%