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

Accepted Solutions

Re: DAX SUM Values of orderlines

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. 

Re: DAX SUM Values of orderlines

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.

 

1.pngStep 1 and Step 22.pngStep 3 & Step 43.pngStep 5

 

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

 

Thanks

 

4 REPLIES 4

Re: DAX SUM Values of orderlines

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. 

Re: DAX SUM Values of orderlines

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.

MMJ369 Frequent Visitor
Frequent Visitor

Re: DAX SUM Values of orderlines

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

Re: DAX SUM Values of orderlines

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.

 

1.pngStep 1 and Step 22.pngStep 3 & Step 43.pngStep 5

 

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

 

Thanks