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

[Vinothsusai]

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.

Could you please advise how we can acheive this

Thanks

Vinoth S

[v-lili6-msft]

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

[Vinothsusai]

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.

Thanks

Vinoth SUSAINATHAN

[v-lili6-msft]

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

[Vinothsusai]

Hi,

Thank you for your reply.

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

Thanks

Vinoth SUSAINATHAN

[v-lili6-msft]

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

[Vinothsusai]

Hi 

Please find the link

https://flexmind-my.sharepoint.com/:u:/g/personal/vsusainathan_flexmind_fr/ESHyf0ZC_CdJvUHNdGFQcwEBu...

Thanks

Vinoth S

[v-lili6-msft · Accepted solution]

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

[Vinothsusai]

Hi,

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

Thanks

Vinoth S