cancel
Showing results for
Did you mean:
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

 orderno item qty line value 123 A 4 40 123 B 3 6 124 A 6 60 125 D 2 2 125 A 1 10

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

I tried SUMX, FILTER, CALCULATE etc.

How do I calculate the order value correctly per order?

Result should be:

 orderno OrderValue 123 6 124 60 125 12

Thanks!

2 ACCEPTED SOLUTIONS

Accepted Solutions
Member

## 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.

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

Member

## 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.

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

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

Thanks

4 REPLIES 4
Member

## 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.

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

Member

## 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.

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
Member

## 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.

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

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

Thanks