topic Re: On Time total order level correct in DAX Commands and Tips

On Time total order level correct

Tavi_ — Wed, 16 Apr 2025 14:35:25 GMT

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())

Re: On Time total order level correct

lbendlin — Wed, 16 Apr 2025 20:32:29 GMT

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))

Re: On Time total order level correct

Tavi_ — Thu, 17 Apr 2025 06:50:39 GMT

Thanks, it is not working in mine dataset yet. Could you explain your steps?

Re: On Time total order level correct

lbendlin — Thu, 17 Apr 2025 19:51:31 GMT

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