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. I have formula like 11 + 12 . i just need to sum values of 11th id amount and 12th id amount values as below screen shot. But it retreives 1th id amount value. Please advise. In the scrrenshot I explained clearly. Please advise.
Thanks
Vinoth SUSAINATHAN
Solved! Go to Solution.
Hi @Vinothsusai
Create a column
formula_alter = IF([Formula]<>BLANK(),[Formula]&"+")
Then create a measure
Measure 2 = VAR MAXF = MAX ( Table1[formula_alter] ) RETURN IF ( MAXF = BLANK (), SUM ( Table1[Amount] ), CALCULATE ( SUM ( Table1[Amount] ), FILTER ( ALL ( Table1 ), CONTAINSSTRINGEXACT ( MAXF, "D" & [LineID] & "+" ) ) ) )
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.
Hi @Vinothsusai
Do you have a dataset as below?
"Formula" ,"Total amount" and "amount" in your pictures are created by you based on the dataset, right?
line id | main amount |
1 | 1 |
2 | |
3 | 2 |
4 | 3 |
5 | |
6 | |
7 | 6 |
8 | 7 |
9 | |
10 | 8 |
11 | 9 |
12 |
Could you give the calculation rule for "Formula" ,"Total amount" and "amount"?
You could add expected values in my simple data example above and share here.
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.
Hi,
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.
Hi @Vinothsusai
Create a column
formula_alter = IF([Formula]<>BLANK(),[Formula]&"+")
Then create a measure
Measure 2 = VAR MAXF = MAX ( Table1[formula_alter] ) RETURN IF ( MAXF = BLANK (), SUM ( Table1[Amount] ), CALCULATE ( SUM ( Table1[Amount] ), FILTER ( ALL ( Table1 ), CONTAINSSTRINGEXACT ( MAXF, "D" & [LineID] & "+" ) ) ) )
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.
Hi,
Thank you for your reply.
Yes. I have a dataset as mentioned below.
Line ID GroupAccount GroupAccountDescription MainAccount ReportLayout.Formula Amount TotalAmount
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 | ||
164 | R613100 | Lease expense | 61320001 | 35624.28 |
Here i need to find Total amount based on the formula.
@v-lili6-msfthad already provided a solution for me. Please see the below ticket url. In the formula I have a problem in ContainsStringExact function
Fornula:
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])))))
Formula field is a string type. While extract the line ID from the formula (for ex: D11 + D12 = 11 + 12. ID 11th Amount value is 0 and ID 12th Amount value is 0 so the total is 0 but the result seems wrong as retrieved ID 1th Amount Value because I am using ContainsStringExact function when it compare D11, it takes as D1 . D1 string with in D11)
To resolve this, I have used another formula. But it works serial IDs for ex: D1+D2+D3+D4+D5 but not in random Ids like D5+D7+ D9+D20 because the formula is looping serially.
Measure Formula = VAR maxf =
MAX ( HANGeneralJournals_Remodify[ReportLayout.Formula] )
VAR sub = SUBSTITUTE ( maxf, "D", "" )
VAR no =
LEN (maxf) - LEN ( SUBSTITUTE ( sub, "+", "" ) )
VAR a =
CALCULATE (
SUM ( HANGeneralJournals_Remodify[Custom Main Amount] ),
FILTER (
ALL ( HANGeneralJournals_Remodify ),
HANGeneralJournals_Remodify[ReportLayout.Line ID] <= MAX ( HANGeneralJournals_Remodify[ReportLayout.Line ID] )
&& HANGeneralJournals_Remodify[ReportLayout.Line ID]
>= MAX ( HANGeneralJournals_Remodify[ReportLayout.Line ID] ) - no
)
)
RETURN
IF ( maxf = BLANK (),SUM (HANGeneralJournals_Remodify[Custom Main Amount]) , a )
Please advise
Thanks
Vinoth S
User | Count |
---|---|
101 | |
90 | |
79 | |
70 | |
70 |
User | Count |
---|---|
112 | |
96 | |
96 | |
75 | |
72 |