Unable to calculate Total amount based on the formula.

[Vinothsusai]

Hi,

I am new into Power BI. Could you please advise.

I am expecting the output like below. Please help me

Data

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

When i use my measure,

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]))))

I got the wrong result for the formula (D10 + D11) in the row Line ID 11, the measure taken D1+D1 instead of D10 + D11. Please see the below screen shot.

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	200

Instead of get 240 (D10+D11), I am getting 200(D1+D1) in lineitemID 11 by using my measure.

Please advise.

Thanks

Vinoht S

[Cmcmahan]

Huh. This is an interesting issue to think about, though trying to actually implement it seems like a nightmare. I applaud you for getting this far.

My quick and easy fix would be to add a delimiter (likely a period or semicolon) after every cell ID in your formula.  So you would have "D1 + D2 " as your formula, and then search for "D"&[LineNo]&" ".  So then it would be looking for "D1 " vs "D10 " instead of finding a match for "D1" in "D10".

I'm honestly surprised that Line 11's measure isn't calculating to 340, since the filter should be matching D1, D10, and D11. 

Actually, looking closer at the current value, if these are actual numbers you've used to calculate, I have no idea how it's summing the result from D1 twice.  The CONTAINSSTRINGEXACT function should be comparing "D10+D11" against each value.  So it should evaluate to true for each number once.  I'm pretty sure that your formula would fail if you set up a formula to calculate D2+D2, returning just the value of D2 for example.

[Cmcmahan]

So I just went ahead and copied the data you provided, and I was right.  Copy/pasting your exact measure, I did get 340 for Line 11, and I got 50 when trying to calculate D2+D2.

As far as fixing your measure, it was very easy to add a delimiter to each of the formulas and then test for "D"&[LineID]&delimiter

[Vinothsusai]

Hi,

Thank you very much for your reply.

Could you please give me an example. 

For ex. D10 + D11

Thanks

Vinoth 

[Cmcmahan]

Well first you have to update all of the values in the formula column to be "D10;+D11;" or with a delimiter of your choice. 

Then you can add the bit in red to fix your issue:

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]&";"))))

[Vinothsusai]

Hi @Cmcmahan,@jdbuchanan71 

Thank you very much for your reply.

@Cmcmahan - your solution works great. I have a doubt how can i calculate formula with the same aggreagated column amount. Could you please advise.

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

[Cmcmahan]

So for that one specific line, you want it to calculate using the TotalAmount instead of the Amount cell?

What indicates that this specific formula should calculate differently from the others?

[Vinothsusai]

Hi,

Thank you for your reply.

Its not specific line. Like that we have many lines to calculate the formula. Its a dynamic calculation based on the total amount.

Thanks

Vinoth SUSAINATHAN

[Cmcmahan]

Ahh, you want to use the value in Total Amount for future calculations. That gets much trickier than using the static Amount column for calculations like your original post indicated.  You would essentially need to recursively calculate all previous amounts to get the proper total. 

DAX measures are not good at using previous results to get current values, since it calculates each cell at the time the measure is called. Using a DAX calculated column also seems unlikely to work for you, since they only refresh when you refresh your entire dataset.

If you're just trying to get grouped subtotals, you can do that with the matrix visualization in PowerBI, without having to mess around with these formulas.

Honestly, it looks like you're trying to attempt some workaround to use PowerBI to do Excel's job.  Why not just use Excel to do Excel's job, and then import results into PowerBI for analysis?

[jdbuchanan71]

@Vinothsusai  Take a look at this post on cascading subtotals.  I think it may be along the lines of what you are trying to accomplish and may help.

https://powerpivotpro.com/2011/09/profit-lossthe-art-of-the-cascading-subtotal/

[jdbuchanan71]

Hello @Vinothsusai 

I think you will need to change the format of your ID column in order to avoid the problem.  Something like:

LineID	Amount	Formula	Total Amount
ID01	100		100
ID02	50	D1+D2	150
ID03	200	D1+D3	300
ID04	70	D2+D4	120
ID05	20	D1+D2+D4	220
ID06	10	D4+D6	80
ID07	20	D5+D6+D7	50
ID10	110	D5+D6+D7	50
ID11	130	D10+D11	200

That way your measure doesn't find '1' twice in '11'