Need to create columns which are depends each other

[Fsciencetech]

Hi folks,

Need your help to create last three column, which are depend to each other. Just copy and paste in your excel so that you can understand how it depends each other.

Date	Qty	Purchase	Sale	Cumulative Qty	Cost Seling Share	Acc Shares- Cost	Per Share Avg Cost
44067	750000	1616300.76	0	=+B2		=+C2	=+G2/B2
44074	1200000	2458775.88	0	=E2+B3		=G2+C3	=+G3/E3
44076	-205623	0	428737.02	=E3+B4	=+B4*H3	=G3+F4
44123	-744377	0	1592215.21	=E4+B5	=+B5*H3	=G4+F5
44126	-300000	0	658446.75	=E5+B6	=+B6*H3	=G5+F6
44129	250000	538766.92	0	=E6+B7		=G6+C7	=+G7/E7
44129	-250000	0	546209.71	=E7+B8	=+B8*H7	=G7+F8	=+G8/E8
44130	1879000	4665623.37	0	=E8+B9		=G8+C9	=+G9/E9
44130	-1629000	0	4127967.18	=E9+B10	=+B10*H9	=G9+F10
44131	-950000	0	2672727.33	=E10+B11	=+B11*H9	=G10+F11

Many thanks for your support.

Please let me know if you have any doubt.

[Anonymous]

Hi@Fsciencetech ，

After testing, we recommend that you implement this requirement in Excel, it is hard to be implemented in Power BI using DAX. If there is anything else you need help with, please feel free to contact us.

Best Regards,

Caitlyn Yan

[Fsciencetech]

Hi Caitlyn,

Thanks for your suggestion.

[Fsciencetech]

Any response please

[Fsciencetech]

These DAX using here 

previousIndex=if(tbsahres[Index]=1,0,CALCULATE(max(tbsahres[Index]),FILTER(tbsahres,tbsahres[Symbol]=EARLIER(tbsahres[Symbol])&&tbsahres[Investor]=EARLIER(tbsahres[Investor])&&tbsahres[Broker]=EARLIER(tbsahres[Broker])&&tbsahres[Index]<EARLIER(tbsahres[Index])))) 

SignType = SIGN(tbsahres[Buy/(Sell)Qty]) 

Acc_Dr = CALCULATE ( SUMX(tbsahres,tbsahres[Dr]), FILTER ( ALLEXCEPT (tbsahres,tbsahres[Investor],tbsahres[Broker],tbsahres[Symbol]), tbsahres[Index] <= EARLIER( tbsahres[Index])&&tbsahres[SignType] = 1&&tbsahres[Dr]<>0) ) 

Rate_Test = if(tbsahres[Dr] <>0, tbsahres[Acc_Dr]/tbsahres[Closing_Stock]) 

CSS =CALCULATE( SUMX(tbsahres,tbsahres[Rate_Test]*tbsahres[Closing_Stock]),SUMMARIZE(tbsahres,tbsahres[Broker],tbsahres[Investor],tbsahres[Symbol]))

[Fsciencetech]

Trying to get last three column as excel but didn't got the solution.

[Fsciencetech]

Hi Folks

Anyone get the chance to open attached excel and found solution in Power BI.

https://onedrive.live.com/edit.aspx?cid=fa47021b37750d6e&page=view&resid=FA47021B37750D6E!1367&parId...

[Fsciencetech]

Here I am trying

Acc_Dr = CALCULATE (

SUMX(tbsahres,tbsahres[Dr]),

FILTER (

ALLEXCEPT (tbsahres,tbsahres[Investor],tbsahres[Broker],tbsahres[Symbol]),

tbsahres[Index] <= EARLIER( tbsahres[Index])&&tbsahres[SignType] =1)

)

WA Cost =

VAR RateTable =

ADDCOLUMNS(

SUMMARIZE(tbsahres,

tbsahres[Investor],tbsahres[Broker],tbsahres[Symbol],tbsahres[Index]

),

"Rate_New", CALCULATE(SUM(tbsahres[Acc_Dr]),FILTER(tbsahres, tbsahres[SignType] = 1))/CALCULATE(SUM(tbsahres[Closing_Stock]),FILTER(tbsahres,tbsahres[SignType] = 1)),

"TotalQty", SUM(tbsahres[Buy/(Sell)Qty])

)

RETURN

SUMX(RateTable, [Rate_New]*[TotalQty]

)

 Here [Sign Type] field has Purchase for 1 and Sales for -1

I am trying but not getting proper result.

May I get any support here from anyone please.

 @amitchandak @PhilipTreacy @PaulDBrown @parry2k 

[PaulDBrown]

@Fsciencetech 

Any chance you can provide sample data or PBIX file and a depiction of the expected result?

[Fsciencetech]

Hi Paul,

Thanks for reponce

Here is the excel file, I need last 4 column in power BI desktop 

https://d.docs.live.net/fa47021b37750d6e/Trade.xlsx 

[Fsciencetech]

One more thing, May be possible we can't create 3 column from K to M because all three are depend to each other. If possible all three, then its fine otherwise K column(CSS) must required as final result.

Thank you so much 

[Fsciencetech]

Hi

I want to explore more my question.

Basically I want to calculate Weightage Average Cost method calculation.

When you paste above table in excel you can see on "Acc Shares-Cost" balance is 0. I need that result

I tried with avg rate on Purchase/Qty and Sale/Qty then calculate value with this average rate*"Cumulative Qty".

but not getting correct result.

Here you can see second last column has 0 values at the end - or - if you sum up third last column with purchase you will get 0 value. I need this result