Items Per Order - DAX Measure

[Anonymous]

Hi Experts

How would you calculate the items per order based on the sample data in the table below..

For the first Order ID we have one item ordered and the distinct count of the Order Id = 1 so 1/1 = 1

Items Per Order = SUM(FACTSalesOrderTable[total_qty_ordered])/DISTINCTCOUNT(FACTSalesOrderTable[increment_id])

QTY	Order ID		Answer
1	10502		1
2	10503		6
3	10503		6
1	10503		6

[amitchandak]

@Anonymous , Based on what I got. I think it should be like

divide(count(FACTSalesOrderTable[Order ID]), distinctcount(FACTSalesOrderTable[Order ID]))

[Anonymous]

Has to be the order qty assoicated with that Order ID...

[Anonymous]

hi Amit so for the second Order ID we have ordered 6 items and our distinctcount of order ID is 1 - hence 6/1 = 6 items per order....logic

[amitchandak]

@Anonymous , do you need lines or qty. I thought lines

Sum qty , distinct count of Order ID

divide(Sum(FACTSalesOrderTable[Qty]), distinctcount(FACTSalesOrderTable[Order ID]))

if you need line below , use count

divide(Sum(FACTSalesOrderTable[Qty]), count(FACTSalesOrderTable[Order ID]))

[Anonymous]

Not getting the right answer.....i am expecting the results as per the table....

[amitchandak · Accepted solution]

@Anonymous , Try like

divide(SumX(filter(allselected(FACTSalesOrderTable) , FACTSalesOrderTable[Order ID] = max(FACTSalesOrderTable[Order ID])), FACTSalesOrderTable[Qty]), distinctcount(FACTSalesOrderTable[Order ID]))