calculate total quantity for each order

[wilson_smyth]

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:

1. its added an additional line 3 for order 2. Order 2 only has 2 order lines.
2. 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

[parry2k]

@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_smyth]

Apologies 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]

@wilson_smyth 

This MEASURE works with your sample data.

Measure =
CALCULATE (
    SUM ( Table1[Quantity] ),
    ALL ( Table1[OrderLineID] ),
    VALUES ( Table1[OrderID] )
)

[wilson_smyth]

thank you, this does work!
I need to understand it so that i learn from this.
Could I ask for an explaination how the dax engine interperts this measure?

Thank you for your help!

[Zubair_Muhammad]

@wilson_smyth 

If you were using the Column OrderID from Table1, this simple measure would have sufficed

Measure = Calculate(sum(Table1[Quantity]),ALL(Table1[OrderLineID]))

i..e to you just wanted to sum for all Line IDs

but because you are using OrderID from Orders Table we needed to use VALUES(Table1[OrderID]) to hide the LineorderIDs which do not exist in Table1

[wilson_smyth]

Thanks @zubair_Mumammad. I understand this, but as my mode works by filtering and slicing using dimensions, this makes Table1 the fact table and i cant store slicable attributes in the fact.

thsi is why i have a seperate orders dimension. but what i dont understand is, in the model i have, how the dax expression you wrote is interperted by the dax engine.