Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Items Per Order - DAX Measure

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...
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    Anonymous , Try like

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

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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's avatar
amitchandak
Super User
5 years ago

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's avatar
    Anonymous
    Not applicable
    5 years ago

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

    • amitchandak's avatar
      amitchandak
      Super User
      5 years ago

      Anonymous , Try like

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