Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to Solution.
@Anonymous , Try like
divide(SumX(filter(allselected(FACTSalesOrderTable) , FACTSalesOrderTable[Order ID] = max(FACTSalesOrderTable[Order ID])), FACTSalesOrderTable[Qty]), distinctcount(FACTSalesOrderTable[Order ID]))
@Anonymous , Based on what I got. I think it should be like
divide(count(FACTSalesOrderTable[Order ID]), distinctcount(FACTSalesOrderTable[Order ID]))
Has to be the order qty assoicated with that Order ID...
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
@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]))
Not getting the right answer.....i am expecting the results as per the table....
@Anonymous , Try like
divide(SumX(filter(allselected(FACTSalesOrderTable) , FACTSalesOrderTable[Order ID] = max(FACTSalesOrderTable[Order ID])), FACTSalesOrderTable[Qty]), distinctcount(FACTSalesOrderTable[Order ID]))
User | Count |
---|---|
98 | |
90 | |
78 | |
71 | |
64 |
User | Count |
---|---|
112 | |
96 | |
95 | |
67 | |
65 |