Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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,
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
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.
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
Hi,
Thank you very much for your reply.
Could you please give me an example.
For ex. D10 + D11
Thanks
Vinoth
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]&";"))))
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
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?
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
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?
@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/
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'
User | Count |
---|---|
94 | |
83 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
62 |