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 1/1 = 1

 

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

 

QTYOrder ID Answer
110502 1
210503 6
310503 6
110503 6
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]))

6 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

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

    divide(count(FACTSalesOrderTable[Order ID]), 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

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