Calculating values based on criteria mentioned in other column

[krishna_1]

For following data is sample of one note and have multiple notes and lines for each row.

Line+Add-Sub and Subtotal are to be calculated based on the criteria given in SubTotal_Indicator, Add, Substract columns using DAX.

Please suggest DAX code to get the values mentioned in Line+Add-Sub and Subtotal columns.

Major issue i am facing for SubTotal because i have to use value calcuted in the same code later for other row subtotal.

Note No.	Line No.	X-factor	SubTotal_Indicator	Add	Substract	NoteLine_Ref	LineBal	Line+Add-Sub	SubTotal
A	5	1				A\5
A	10	1		A\330:A\380| A\450		A\10		32,000
A	20	1		A\340| A\460	A\330	A\20		13,000
A	30	1		A\350| A\470		A\30		16,000
A	40	1		A\360| A\480		A\40		18,000
A	50	1		A\370| A\490		A\50		20,000
A	60	1		A\380| A\500		A\60		22,000
A	70	1		A\390| A\510		A\70		24,000
A	100	1		A\420| A\540		A\100		26,000
A	150	1	SubTotal	A\5: A\150		A\150			1,71,000
A	200	-1				A\200	1,000	1,000
A	300	1	SubTotal	A\150	A\200	A\300			1,70,000
A	310	1				A\310
A	320	1				A\320
A	330	1				A\330	1,000	1,000
A	340	1				A\340	2,000	2,000
A	350	1				A\350	3,000	3,000
A	360	1				A\360	4,000	4,000
A	370	1				A\370	5,000	5,000
A	380	1				A\380	6,000	6,000
A	390	1				A\390	7,000	7,000
A	420	1				A\420	8,000	8,000
A	430	1	SubTotal	A\320: A\430		A\430			36,000
A	440	1				A\440
A	450	1				A\450	11,000	11,000
A	460	1				A\460	12,000	12,000
A	470	1				A\470	13,000	13,000
A	480	1				A\480	14,000	14,000
A	490	1				A\490	15,000	15,000
A	500	1				A\500	16,000	16,000
A	510	1				A\510	17,000	17,000
A	540	1				A\540	18,000	18,000
A	550	1	SubTotal	A\440: A\550		A\550			1,16,000
A	560	1	SubTotal	A\430	A\550	A\560			-80,000

@Greg_Deckler please help me out in this. 

[Greg_Deckler]

@krishna_1 I'm with @Anonymous on this one, I have no idea what is going on. 

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Anonymous]

Hi @krishna_1 ,

Unfortunately I don't see how the results you were expecting were obtained, I hope you can give a more detailed explanation of the instructions so that we can better help you solve the problem.

Best Regards,

Clara Gong

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[krishna_1]

Hi @Anonymous , @Greg_Deckler, 

In above data, I am trying to calculate values in Column “Line+Add-Sub” and Column “Subtotal”.

Column “NoteLine_Ref” represents each row no. in the table and Column “LineBal” has corresponding values.

In some cases, I have to add values of other rows to the values in Column “LineBal” to arrive final value for each row of Column “NoteLine_Ref”. The row references are mentioned in Column “Add” and Column "Substract”.

E.g.: 1. Row A\10 has A\330:A\380| A\450  mentioned in Column “Add”. This means that to arrive final value for  Row A\10, values of rows from A\330 to A\380 and A\450 are to be added. The values of these rows are    1,000  2,000   3,000   4,000   5,000   6,000   11,000. The total of these is 32000 (mentioned in Column “Line+Add-Sub”).

Further, Column “NoteLine_Ref” has certain subtotal rows which are identified by mentioning in column “SubTotal_Indicator”. These totals the values in column “Line+Add-Sub”. Here, the subtotal calculated earlier may be used in subtotals afterwards. Like in Row A\300, the earlier subtotal A\150 is to be added and value of A\200 is to be deducted as mentioned in “Add” and “Substract” columns.

If any further clarity is required pls revert