Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
krishna_1
Regular Visitor

Calculating values based on criteria mentioned in other column

For following data is sample of one note and have multiple notes and lines for each row.

Line+Add-Sub and Subtotal are to be calculated based on the criteria given in SubTotal_Indicator, Add, Substract columns using DAX.

Please suggest DAX code to get the values mentioned in Line+Add-Sub and Subtotal columns.

Major issue i am facing for SubTotal because i have to use value calcuted in the same code later for other row subtotal.

 

Note No.Line No.X-factorSubTotal_IndicatorAddSubstractNoteLine_RefLineBalLine+Add-SubSubTotal
A51   A\5   
A101 A\330:A\380| A\450 A\10           32,000 
A201 A\340| A\460A\330A\20           13,000 
A301 A\350| A\470 A\30           16,000 
A401 A\360| A\480 A\40           18,000 
A501 A\370| A\490 A\50           20,000 
A601 A\380| A\500 A\60           22,000 
A701 A\390| A\510 A\70           24,000 
A1001 A\420| A\540 A\100           26,000 
A1501SubTotalA\5: A\150 A\150   1,71,000
A200-1   A\200   1,000            1,000 
A3001SubTotalA\150A\200A\300   1,70,000
A3101   A\310   
A3201   A\320   
A3301   A\330   1,000            1,000 
A3401   A\340   2,000            2,000 
A3501   A\350   3,000            3,000 
A3601   A\360   4,000            4,000 
A3701   A\370   5,000            5,000 
A3801   A\380   6,000            6,000 
A3901   A\390   7,000            7,000 
A4201   A\420   8,000            8,000 
A4301SubTotalA\320: A\430 A\430      36,000
A4401   A\440   
A4501   A\450 11,000          11,000 
A4601   A\460 12,000          12,000 
A4701   A\470 13,000          13,000 
A4801   A\480 14,000          14,000 
A4901   A\490 15,000          15,000 
A5001   A\500 16,000          16,000 
A5101   A\510 17,000          17,000 
A5401   A\540 18,000          18,000 
A5501SubTotalA\440: A\550 A\550   1,16,000
A5601SubTotalA\430A\550A\560      -80,000

 

@Greg_Deckler please help me out in this. 

3 REPLIES 3
Greg_Deckler
Super User
Super User

@krishna_1 I'm with @Anonymous on this one, I have no idea what is going on. 

 

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Hi @krishna_1 ,

 

Unfortunately I don't see how the results you were expecting were obtained, I hope you can give a more detailed explanation of the instructions so that we can better help you solve the problem.

 

 

Best Regards,

Clara Gong

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

Hi @Anonymous , @Greg_Deckler

In above data, I am trying to calculate values in Column “Line+Add-Sub” and Column “Subtotal”.

Column “NoteLine_Ref” represents each row no. in the table and Column “LineBal” has corresponding values.

In some cases, I have to add values of other rows to the values in Column “LineBal” to arrive final value for each row of Column “NoteLine_Ref”. The row references are mentioned in Column “Add” and Column "Substract”.

E.g.: 1. Row A\10 has A\330:A\380| A\450  mentioned in Column “Add”. This means that to arrive final value for  Row A\10, values of rows from A\330 to A\380 and A\450 are to be added. The values of these rows are    1,000  2,000   3,000   4,000   5,000   6,000   11,000. The total of these is 32000 (mentioned in Column “Line+Add-Sub”).

Further, Column “NoteLine_Ref” has certain subtotal rows which are identified by mentioning in column “SubTotal_Indicator”. These totals the values in column “Line+Add-Sub”. Here, the subtotal calculated earlier may be used in subtotals afterwards. Like in Row A\300, the earlier subtotal A\150 is to be added and value of A\200 is to be deducted as mentioned in “Add” and “Substract” columns.

If any further clarity is required pls revert

 
 
 
 
 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.