cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Vinothsusai Regular Visitor
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)

21.png

 

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.

 

Could you please advise how we can acheive this

 

Thanks

Vinoth S

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
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:

1.JPG

 

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.

View solution in original post

8 REPLIES 8
Community Support Team
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:

1.JPG

 

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.
Vinothsusai Regular Visitor
Regular Visitor

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

Hi,

Thank you very much for your reply.

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

Please see the below screen shot.

 

27.png

 

Thanks

Vinoth SUSAINATHAN

Community Support Team
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.
Vinothsusai Regular Visitor
Regular Visitor

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

Hi,

Thank you for your reply.

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

 


28.png

Thanks

Vinoth SUSAINATHAN

Community Support Team
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.

You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.

 

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.
Vinothsusai Regular Visitor
Regular Visitor

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

Community Support Team
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:

1.JPG

 

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.

View solution in original post

Vinothsusai Regular Visitor
Regular Visitor

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

Hi,

Thank you very much for your answer. It works fine. 

 

 

Thanks

Vinoth S

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

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!

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?

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

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!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors