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

[Vinothsusai]

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.

Could you please advise how we can acheive this below,

Thanks

Vinoth SUSAINATHAN

[v-frfei-msft · Accepted solution]

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.

[Vinothsusai]

Thnak you very much

[Vinothsusai]

Hi,

Thank you very much for your answer. 

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 )

Could you please advise.

Thanks

Vinoth SUSAINATHAN

[parry2k]

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

[Vinothsusai]

Hi,

Thank you for your reply.

It can be more than 2 as well.

Thanks

Vinoth SUSAINATHAN