Calculate total value based on the formula with the same aggreagated total column

[Vinothsusai]

Hi,

I am using a measure to find the total amount based on the formula in the table below.Here I have a doubt how can i calculate formula with the same aggreagated column amount. Could you please advise.

Formula:

Measure Formula = VAR maxf =

MAX ( HANGeneralJournals_Remodify[ReportLayout.Formula])

RETURN

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

For example.

i take 25th line item. The formula is  D9|+D12|+D15|+D18|+D21|+D24|

For now its calculating Amount column values by using above measure= 0 + 0 + 0 + 0 + 0 + 0 = 0

But i have to take Total Amount Measure value  ie -69944.87 + 0  -13086.25 -95366.48 + 27602.27-10226.1 = 295150.290000

Please see the table below

Line ID	Group Account	Group Account Description	MainAccount	ReportLayout.Formula	Amount	Total Amount
1	R701000	Gross sales	70100000		148165942.6	148165942.6
2	R70100T	Gross sales		D1|	0	148165942.6
3	R708001	Commissions to retail partner	65100000		0	0
4	R708002	Regul commission to retail partner			0	0
5	R70800T	Sales allowances		D3|+D4|	0	0
6	R_NS	Net sales		D4|+D5|	0	0
7	R601001	Fish purchase			0	0
8	R603001	Change in fish inventory	60310001		-69944.87	-69944.87
9	R601001T	Fish & and other sea products		D7|+D8|	0	-69944.87
10	R601002	Meat purchase			0	0
11	R603002	Change in meat inventory			0	0
12	R601002T	Meat		D10|+D11|	0	0
13	R601003	Fruits & vegetables purchase			0	0
14	R603003	Change in fruits & vegetables inventory	60310003		-13086.25	-13086.25
15	R601003T	Fruits & vegetables		D13|+D14|	0	-13086.25
16	R601004	Grocery purchase			0	0
17	R603004	Change in grocery products inventory	60310004		-95366.48	-95366.48
18	R601004T	Grocery		D16|+D17|	0	-95366.48
19	R601005	Finished goods purchase	60700001		456171.72	456171.72
20	R603005	Change in finished goods inventory	60370000		27602.27	27602.27
21	R601005T	Finished goods		D19|+D20|	0	483773.99
22	R601006	Other products purchase			0	0
23	R603006	Change in other product inventory	60310005		-10226.1	-10226.1
24	R601006T	Other products		D22|+D23|	0	-10226.1
25	R_FOOD	Food		D9|+D12|+D15|+D18|+D21|+D24|	0	0
26	R602001	Packaging purchases	60261000		2204347.66	2204347.66

Thanks

Vinoht S

[v-juanli-msft · Accepted solution]

Hi @Vinothsusai 

you could apply it in specific rows.

Measure 2 =
VAR maxf =
    MAX ( Table1[ReportLayout.Formula] )
RETURN
    IF (
        maxf = BLANK (),
        SUM ( Table1[Amount] ),
        CALCULATE (
            SUM ( Table1[Amount] ),
            FILTER ( ALL ( Table1 ), CONTAINSSTRING ( maxf, "D" & [Line ID] & "|" ) )
        )
    )

Measure 3 =
VAR maxf =
    MAX ( Table1[ReportLayout.Formula] )
RETURN
    IF (
        MAX ( Table1[Line ID] ) <> 25,
        [Measure 2],
        SUMX (
            FILTER ( ALL ( Table1 ), CONTAINSSTRING ( maxf, "D" & [Line ID] & "|" ) ),
            [Measure 2]
        )
    )

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Vinothsusai]

Thank you. It works.