Unable to find Total Amount based on the formula. Problem in ContainsStringExact function

[Vinothsusai]

Hi,

I have a problem when i sum the formula in below table. Here Formula field is refering to LineID. For ex. D3 is referring to Line ID 3. So i need to point 3rd LineItem Amount value (1952934). Like that i need to find total Amount based on the formula.

Please see the below table.

Line ID   GroupAccount   GroupAccountDescription   MainAccount   ReportLayout.Formula   Amount 

1	R701000	Gross sales	70100000		1925702
3	R708001	Commissions to retail partner	65100000	D3	1952934
35	R641001	Chef	64110001		-75.2
36	R641002	Assistant	64110002	D3+D35+D36	111478
40	R645001	Chef	64510001	D1+D36	-1698.72
41	R645002	Assistant	64510002		21069.99
46	R695000	Subsidies	64910000		62371.96
47	R621000	Temporary worker	62110000	D40+D47	1920000
48	R647000	Medical fees	64750000	D41+D47+D48	7595.4
50	R615001	Cleaning Products	60222000		499
55	R615005	Maintenance contract (preventive)	61560000		140157.9
60	R623003	Other marketing PoS	62310000	D41+D47+D48+D60	7686.54
62	R616000	Insurance	61610000		13146.64
63	R622001	Controls	62260005	D60+D62	1060
68	R613001	Lease expense - External party	61320001		35624.28
136	R623530	Agency Compensation	62300000	D68+D136	88372.66
137	R623540	Catalogues / Brochures	62360000		180617.9
139	R628500	Others	62310000	D137+D139	7686.54
142	R622620	Audit fees	62260003		2700
155	R622820	Official Acts	62270001		23195.7

 i have a problem in ContainsStringExact function in formula. It doesnt match exact lineItemID. Could you please advise.

Measure Formula = VAR maxf =

MAX ( HANGeneralJournals_Remodify[ReportLayout.Formula])

RETURN

IF ( maxf = BLANK () ,SUM(HANGeneralJournals_Remodify[Custom Main Amount]), CALCULATE(SUM(HANGeneralJournals_Remodify[Custom Main Amount]),FILTER(ALL(HANGeneralJournals_Remodify),ISBLANK([ReportLayout.Line ID])=FALSE()&&CONTAINSSTRINGEXACT(maxf,"D"&[ReportLayout.Line ID]))))

Thanks

Vinoht SUSAINATHAN

[Greg_Deckler]

I read this through a couple times but wasn't able to make out what you are going for here. Can you provide an example of the expected output?

[Vinothsusai]

Hi,

Thank you for your reply.

I am expecting the output like below. Please help me

Data

LineID	Amount	Formula	Total Amount
1	100		100
2	50	D1+D2	150
3	200	D1+D3	300
4	70	D2+D4	120
5	20	D1+D2+D4	220
6	10	D4+D6	80
7	20	D5+D6+D7	50
10	110	D5+D6+D7	50
11	130	D10+D11	240

Please advise.

Thanks

Vinoht S