cancel
Showing results for
Did you mean:
Vinothsusai Frequent Visitor

Calculate formula column based on Id referred column - power bi desktop

Hi ,

I am new into power bi.

I have Formula column that each value is referred to the Id column. I need to caculate total amount based on the formula column. Thanks

Vinoth SUSAINATHAN

1 ACCEPTED SOLUTION

Accepted Solutions Community Support Team

Re: Calculate formula column based on Id referred column - power bi desktop

Hi @Vinothsusai ,

To create a measure as below.

```Measure =
VAR maxf =
MAX ( Table1[formula -extract] )
VAR no =
LEN ( maxf ) - LEN ( SUBSTITUTE ( maxf, "+", "" ) )
VAR a =
CALCULATE (
SUM ( Table1[Amount] ),
FILTER (
ALL ( Table1 ),
Table1[Line ID] <= MAX ( Table1[Line ID] )
&& Table1[Line ID]
>= MAX ( Table1[Line ID] ) - no
)
)
RETURN
IF ( maxf = BLANK (), BLANK (), a )
```

Or a calculated column as below.

```Column =
VAR aa =
Table1[Line ID]
- (
LEN ( Table1[formula -extract] )
- LEN ( SUBSTITUTE ( Table1[formula -extract], "+", "" ) )
)
VAR co =
CALCULATE (
SUM ( Table1[Amount] ),
FILTER (
Table1,
Table1[Line ID] >= aa
&& Table1[Line ID] <= EARLIER ( Table1[Line ID] )
)
)
RETURN
IF ( Table1[formula -extract] = BLANK (), BLANK (), co )
``` Pbix as attached.

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5 Super User

Re: Calculate formula column based on Id referred column - power bi desktop

@Vinothsusai does formula always going to be sum of  two rows or it can be more than 2 as well?

Proud to be a Datanaut! Kudos Help Connect with me on Linkedin. Feel free to email me with any of your BI needs.

Vinothsusai Frequent Visitor

Re: Calculate formula column based on Id referred column - power bi desktop

Hi,

It can be more than 2 as well.

Thanks

Vinoth SUSAINATHAN Community Support Team

Re: Calculate formula column based on Id referred column - power bi desktop

Hi @Vinothsusai ,

To create a measure as below.

```Measure =
VAR maxf =
MAX ( Table1[formula -extract] )
VAR no =
LEN ( maxf ) - LEN ( SUBSTITUTE ( maxf, "+", "" ) )
VAR a =
CALCULATE (
SUM ( Table1[Amount] ),
FILTER (
ALL ( Table1 ),
Table1[Line ID] <= MAX ( Table1[Line ID] )
&& Table1[Line ID]
>= MAX ( Table1[Line ID] ) - no
)
)
RETURN
IF ( maxf = BLANK (), BLANK (), a )
```

Or a calculated column as below.

```Column =
VAR aa =
Table1[Line ID]
- (
LEN ( Table1[formula -extract] )
- LEN ( SUBSTITUTE ( Table1[formula -extract], "+", "" ) )
)
VAR co =
CALCULATE (
SUM ( Table1[Amount] ),
FILTER (
Table1,
Table1[Line ID] >= aa
&& Table1[Line ID] <= EARLIER ( Table1[Line ID] )
)
)
RETURN
IF ( Table1[formula -extract] = BLANK (), BLANK (), co )
``` Pbix as attached.

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

Re: Calculate formula column based on Id referred column - power bi desktop

Thnak you very much

Highlighted
Vinothsusai Frequent Visitor

Re: Calculate formula column based on Id referred column - power bi desktop

Hi,

Your mentioned formula works fine for the sequence numbers (For ex 4+4+6...).

If it has non sequence number (For ex. 215+248+250+251+273). it not works. I tried to modify the formula that not works. Could you please advise. Formula:

Measure =
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[AccountingCurrencyAmount]  ),
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 (),0 , a )