Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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]))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |