Forum Discussion
Post Patroncalculate total quantity for each order
I am trying to calculate the total quantity of items on all lines of an order.
So each line will show the quantity for the orderline, and the total order quantity.
Model is simple, an order dimension and a fact table at the orderline granularity.
Heres where i have gotten to. I want to see each order line, but with a measure that shows the total sum quantity for that order.
I created TO2 to give the orderTotal. The issue with the measure is:
- its added an additional line 3 for order 2. Order 2 only has 2 order lines.
- This additional orderline has no quantity, presumably because there is no order line 3 on order 2.
I am aware that making the relationship bidirectional will fix the issue, but my actual model is quite complex and i dont want to use bidirectional relationships as i dont need ambiguity issues.
Im also aware that using the orderID from a dimension table makes things more difficult, but this is how it appears in the model.
Appreciate help understanding what i am doing wrong here.
A pbix is at this link
6 Replies
- parry2k
Super User
wilson_smyth i looked at your pbix but not fully clear about your requirement, can you put what your expected result is based on pbix you shared and where you see the issue?
wilson_smythApologies for the lack of clarity, I have updated the original post with a screenshot of the report & two points explaining the issue with it that i am stuck on.
Please let me know if there is enough information here as i can add whatever is required.Thanks
- Zubair_Muhammad
Community Champion
This MEASURE works with your sample data.
Measure = CALCULATE ( SUM ( Table1[Quantity] ), ALL ( Table1[OrderLineID] ), VALUES ( Table1[OrderID] ) )
