Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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-factor | SubTotal_Indicator | Add | Substract | NoteLine_Ref | LineBal | Line+Add-Sub | SubTotal |
A | 5 | 1 | A\5 | ||||||
A | 10 | 1 | A\330:A\380| A\450 | A\10 | 32,000 | ||||
A | 20 | 1 | A\340| A\460 | A\330 | A\20 | 13,000 | |||
A | 30 | 1 | A\350| A\470 | A\30 | 16,000 | ||||
A | 40 | 1 | A\360| A\480 | A\40 | 18,000 | ||||
A | 50 | 1 | A\370| A\490 | A\50 | 20,000 | ||||
A | 60 | 1 | A\380| A\500 | A\60 | 22,000 | ||||
A | 70 | 1 | A\390| A\510 | A\70 | 24,000 | ||||
A | 100 | 1 | A\420| A\540 | A\100 | 26,000 | ||||
A | 150 | 1 | SubTotal | A\5: A\150 | A\150 | 1,71,000 | |||
A | 200 | -1 | A\200 | 1,000 | 1,000 | ||||
A | 300 | 1 | SubTotal | A\150 | A\200 | A\300 | 1,70,000 | ||
A | 310 | 1 | A\310 | ||||||
A | 320 | 1 | A\320 | ||||||
A | 330 | 1 | A\330 | 1,000 | 1,000 | ||||
A | 340 | 1 | A\340 | 2,000 | 2,000 | ||||
A | 350 | 1 | A\350 | 3,000 | 3,000 | ||||
A | 360 | 1 | A\360 | 4,000 | 4,000 | ||||
A | 370 | 1 | A\370 | 5,000 | 5,000 | ||||
A | 380 | 1 | A\380 | 6,000 | 6,000 | ||||
A | 390 | 1 | A\390 | 7,000 | 7,000 | ||||
A | 420 | 1 | A\420 | 8,000 | 8,000 | ||||
A | 430 | 1 | SubTotal | A\320: A\430 | A\430 | 36,000 | |||
A | 440 | 1 | A\440 | ||||||
A | 450 | 1 | A\450 | 11,000 | 11,000 | ||||
A | 460 | 1 | A\460 | 12,000 | 12,000 | ||||
A | 470 | 1 | A\470 | 13,000 | 13,000 | ||||
A | 480 | 1 | A\480 | 14,000 | 14,000 | ||||
A | 490 | 1 | A\490 | 15,000 | 15,000 | ||||
A | 500 | 1 | A\500 | 16,000 | 16,000 | ||||
A | 510 | 1 | A\510 | 17,000 | 17,000 | ||||
A | 540 | 1 | A\540 | 18,000 | 18,000 | ||||
A | 550 | 1 | SubTotal | A\440: A\550 | A\550 | 1,16,000 | |||
A | 560 | 1 | SubTotal | A\430 | A\550 | A\560 | -80,000 |
@Greg_Deckler please help me out in this.
@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.
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.
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
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
9 | |
8 | |
7 | |
6 |
User | Count |
---|---|
15 | |
13 | |
11 | |
9 | |
8 |