cancel
Showing results for
Did you mean:
Regular Visitor

## Calculate formula column based on Id referred column(Non Sequence ID number) - power bi desktop

Hi,

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

For ex:

ReportLayout.Formula(D215) = Line ID(215)

The below formula works fine for the sequence numbers

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 )

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.

Thanks

Vinoth S

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Calculate formula column based on Id referred column(Non Sequence ID number) - power bi desktop

hi, @Vinothsusai

Just adjust the formula as below:

```Measure 4 =
VAR maxf =
MAX ( HANGeneralJournals_Remodify[Column 3])

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

Result:

For example, in your sample pbix file, the formula is "D231+D276+D284+D281+D282+D283", So I use a custom column 3 in the example:

Best Regards,

Lin

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

## Re: Calculate formula column based on Id referred column(Non Sequence ID number) - power bi desktop

HI, @Vinothsusai

You could use this simple formula:

```Measure =
VAR maxf =
MAX ( HANGeneralJournals_Remodify[ReportLayout.Formula] )

RETURN
IF ( maxf = BLANK (),0 , CALCULATE(SUM(HANGeneralJournals_Remodify[AccountingCurrencyAmount]),FILTER(ALL(HANGeneralJournals_Remodify),CONTAINSSTRINGEXACT(maxf,[ReportLayout.Line ID]))) ) ```

here is simple sample example:

Best Regards,

Lin

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

## Re: Calculate formula column based on Id referred column(Non Sequence ID number) - power bi desktop

Hi,

The calculated measure values are not correct. Could you please advise where i did mistake in the report.

Please see the below screen shot.

Thanks

Vinoth SUSAINATHAN

Community Support Team

## Re: Calculate formula column based on Id referred column(Non Sequence ID number) - power bi desktop

hi, @Vinothsusai

Do you have done any filter by slicer, if so try ALLSELECTED instead of ALL in this formula.

And if you still have the problem, please share a sample pbix file.

Best Regards,

lin

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

## Re: Calculate formula column based on Id referred column(Non Sequence ID number) - power bi desktop

Hi,

I have tried with ALLSELECTED. It seems something wrong in calculated result. Could you please advise.

Thanks

Vinoth SUSAINATHAN

Community Support Team

## Re: Calculate formula column based on Id referred column(Non Sequence ID number) - power bi desktop

hi, @Vinothsusai

Please share the sample pbix file.

Best Regards,

Lin

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

## Re: Calculate formula column based on Id referred column(Non Sequence ID number) - power bi desktop

Community Support Team

## Re: Calculate formula column based on Id referred column(Non Sequence ID number) - power bi desktop

hi, @Vinothsusai

Just adjust the formula as below:

```Measure 4 =
VAR maxf =
MAX ( HANGeneralJournals_Remodify[Column 3])

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

Result:

For example, in your sample pbix file, the formula is "D231+D276+D284+D281+D282+D283", So I use a custom column 3 in the example:

Best Regards,

Lin

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

## Re: Calculate formula column based on Id referred column(Non Sequence ID number) - power bi desktop

Hi,

Thanks

Vinoth S

Announcements

#### Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!