Forum Discussion
On Time total order level correct
See the picture above. How do I get the correct of amount of total orders on time (22% | 2 orders on time, total of 9 orders)?
Dump in Excel.
| Order | Order Line | Sales Order Count | Sales Order Count Lines | Sales Order Count Lines On Time | Sales Order Lines On Time | Sales Order On Time |
| 0000114973 | 0000114973_000040 | 1 | 1 | 0% | ||
| 0000114973 | 0000114973_000050 | 1 | 1 | 0% | ||
| 0000115825 | 0000115825_000040 | 1 | 1 | 0% | ||
| 0000119360 | 0000119360_000060 | 1 | 1 | 0% | ||
| 0000120578 | 0000120578_000080 | 1 | 1 | 0% | ||
| 0000121304 | 0000121304_000080 | 1 | 1 | 1 | 100% | 100% |
| 0000121674 | 0000121674_000050 | 1 | 1 | 0% | ||
| 0000121674 | 0000121674_000060 | 1 | 1 | 0% | ||
| 0000123046 | 0000123046_000020 | 1 | 1 | 1 | 100% | 100% |
| 0000123046 | 0000123046_000030 | 1 | 1 | 1 | 100% | 100% |
| 0000123046 | 0000123046_000070 | 1 | 1 | 0% | ||
| 0000123047 | 0000123047_000010 | 1 | 1 | 1 | 100% | 100% |
| 0000123047 | 0000123047_000020 | 1 | 1 | 0% | ||
| 0000123908 | 0000123908_000050 | 1 | 1 | 1 | 100% | 100% |
Currently I'm using this DAX formula
Sales Order On Time = IF([Sales Order Count] = 1, IF([Sales Order Count Lines] = [Sales Order Count Lines In Time], 1, 0), BLANK())Sales Order On Time =
VAR a =
ADDCOLUMNS (
VALUES ( 'Table'[Order] ), -- get distinct order numbers
"r", CALCULATE ( COUNTROWS ( 'Table' ) ), -- get number of line items per order
"o",
CALCULATE (
COUNTROWS ( 'Table' ),
'Table'[Sales Order Count Lines On Time] = 1 -- get number of line items per order that are on time
)
)
RETURN
COUNTROWS ( FILTER ( a, [r] = [o] && [o] > 0 ) ) -- count the number of orders where all line items are on time (the number of "on time" line items is not zero, and matches the number of total order line items
3 Replies
you are using a calculated column without aggregation. You probably want to use a measure instead.
Sales Order On Time = var a = ADDCOLUMNS(VALUES('Table'[Order]),"r",calculate(COUNTROWS('Table')),"o",CALCULATE(COUNTROWS('Table'),'Table'[Sales Order Count Lines On Time]=1)) return countrows(filter(a,[r]=[o] && [o]>0))Thanks, it is not working in mine dataset yet. Could you explain your steps?
Sales Order On Time =
VAR a =
ADDCOLUMNS (
VALUES ( 'Table'[Order] ), -- get distinct order numbers
"r", CALCULATE ( COUNTROWS ( 'Table' ) ), -- get number of line items per order
"o",
CALCULATE (
COUNTROWS ( 'Table' ),
'Table'[Sales Order Count Lines On Time] = 1 -- get number of line items per order that are on time
)
)
RETURN
COUNTROWS ( FILTER ( a, [r] = [o] && [o] > 0 ) ) -- count the number of orders where all line items are on time (the number of "on time" line items is not zero, and matches the number of total order line items
