Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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:
orderno | OrderValue |
123 | 6 |
124 | 60 |
125 | 12 |
Thanks!
Solved! Go to Solution.
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.
Hi @MMJ369,
To create new tables you need to follow the below steps :
I am also attaching the sequence of steps you can follow.
By this you can achive a New table with OrderNumber and Sum of OrderValue/Line Number.
Thanks
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.
Hi @MMJ369,
To create new tables you need to follow the below steps :
I am also attaching the sequence of steps you can follow.
By this you can achive a New table with OrderNumber and Sum of OrderValue/Line Number.
Thanks
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |