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

 

Could you please advise how we can acheive this below,

 

1.png

 

Thanks

Vinoth SUSAINATHAN

1 ACCEPTED SOLUTION

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

Capture.PNG

 

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
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?





Did I answer your question? Mark my post as a solution.

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




 




Vinothsusai Frequent Visitor
Frequent Visitor

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

Hi,

Thank you for your reply.

It can be more than 2 as well.

 

Thanks

Vinoth SUSAINATHAN

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

Capture.PNG

 

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
Frequent Visitor

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

Thnak you very much

Highlighted
Vinothsusai Frequent Visitor
Frequent Visitor

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

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.

 

21.png

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