Forum Discussion

Tavi_'s avatar
Tavi_
Frequent Visitor
1 year ago
Solved

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 Sa...
Need Help
  • lbendlin's avatar
    lbendlin
    1 year ago

    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

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
1 year ago

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

 

 

 

Tavi_'s avatar
Tavi_
Frequent Visitor
1 year ago

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

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    1 year ago

    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