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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MMJ369
Frequent Visitor

DAX SUM Values of orderlines

Hi Guys,

 

It is probably very basic but I can not get it right in Power BI. Not even with all the examples on here.

 

I have a table with a row per orderline with ordernumber, item, quantity and value of that line.

 

I now need to calculate the value per order by adding up all the line item values per order.

 

Table

ordernoitemqtyline value
123A440
123B36
124A660
125D22
125A110

 

I created a table with the unique orderno’s but the order value is the total of everything on every line.

OrderValue = ADDCOLUMNS(SUMMARIZE(ORDERLINES,ORDERLINES[ORDERNO]),"OrderValue",SUM(ORDERLINES[LINEVALUE]))

I tried SUMX, FILTER, CALCULATE etc.

 

How do I calculate the order value correctly per order?

 

Result should be:

ordernoOrderValue
1236
12460
125

12

 

Thanks!

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

Hi  @MMJ369,

 

This is acchived without using any DAX function, you just need to pull the Order number and the Qty, the column will be aggrigated automatically.

 

 

1.png

 

Let me know if I got it right as you required. 

View solution in original post

Anonymous
Not applicable

Hi @MMJ369,

 

To create new tables you need to follow the below steps :

 

  1. Click on the Edit Queries window in the Home Panel.
  2. Right click on your table and create a duplicate table.
  3. Go to the Group by button in home Pagen in Powre Query Editor Window.
  4. Sum up your LineValue Column based on your OrderNumber.
  5. Click on Close & Apply.

I am also attaching the sequence of steps you can follow.

 

Step 1 and Step 2Step 1 and Step 2Step 3 & Step 4Step 3 & Step 4Step 5Step 5

 

By this you can achive a New table with OrderNumber and Sum of OrderValue/Line Number.

 

Thanks

 

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

Hi  @MMJ369,

 

This is acchived without using any DAX function, you just need to pull the Order number and the Qty, the column will be aggrigated automatically.

 

 

1.png

 

Let me know if I got it right as you required. 

Thanks jayant_patel. Excellent, I can use this.
If you do know how to create a new table with ordernumbers and ordervalue it would be great if you could explain that as well just for learning.
Thanks
Anonymous
Not applicable

Hi @MMJ369,

 

To create new tables you need to follow the below steps :

 

  1. Click on the Edit Queries window in the Home Panel.
  2. Right click on your table and create a duplicate table.
  3. Go to the Group by button in home Pagen in Powre Query Editor Window.
  4. Sum up your LineValue Column based on your OrderNumber.
  5. Click on Close & Apply.

I am also attaching the sequence of steps you can follow.

 

Step 1 and Step 2Step 1 and Step 2Step 3 & Step 4Step 3 & Step 4Step 5Step 5

 

By this you can achive a New table with OrderNumber and Sum of OrderValue/Line Number.

 

Thanks

 

Anonymous
Not applicable

Also make sure that your for your Order Number there is no aggrigation.

 

Go to Modeling and in the Properties section > Default Summarization : Don't Summarize.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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