Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
Solved! Go to 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.
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.
Thnak you very much
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:
Could you please advise.
Thanks
Vinoth SUSAINATHAN
@Vinothsusai does formula always going to be sum of two rows or it can be more than 2 as well?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hi,
Thank you for your reply.
It can be more than 2 as well.
Thanks
Vinoth SUSAINATHAN
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
113 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |