Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Measure to determine order count with filter

HI everyone, I need some help.

 

I am trying to determine the OTIF measure of a table that has detailed order data. The structure is the following:

 

DATEOrderNoSKUQTYQTY_ORDEREDQTY_DEILVERDQTY_NOT_DELIVEREDCATEGORYCPGROUTE

 

The QTY field could be a positive or negative quantity that indicates if the SKU (an article or product) was ordered or rejected. Some sample data are this:

In order to get OTIF Measure (#Orders Fully Delivered / #Total Orders), I need to get the count of orders that match the condition that QTY_ORDERED == QTY_DELIVERED, counting by order_no.

I've created this measure:

In Full = CALCULATE(DISTINCTCOUNT(PruebaOrdenesOper[NRO_PEDIDO]), FILTER(PruebaOrdenesOper, SUM(PruebaOrdenesOper[PROD_PEDIDOS]) == SUM(PruebaOrdenesOper[PROD_ENTREGADOS])))
 
But this doesn't work. When I see all the data with OrderNo and quantities summarized, this measure seems to work ok,
But when I going to count without orderNo (only by date), the measure doesn't work anymore:
 
My idea for OTIF is to do after all a division InFull / COUNT_PEDIDOS. So I Need to determine the count of orders that match these condition (QTY_ORDERED == QTY_DELIVERED). This measure it depends on the qualifier, you could see OTIF by CPG, by ROUTE, By Model, Brand, etc. 
 
Anyone can help me?
Thanks!
General Comment
Need Help
Tips and Tricks

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous , Create a new measure on the top of it and use

     

    sumx(addcolumns( Summarize(PruebaOrdenesOper,PruebaOrdenesOper[NRO_PEDIDO],Date[Fecha]), "_1", [In Full]) ,[_1])

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi  Anonymous ,

    According to your picture, when you remove [orderNo], the entire visual object will be grouped with [FECHA] for aggregation, but your [FECHA] is the same, so only one row of aggregated data will be displayed, When your [orderNo] exists, the entire visual object will be grouped and aggregated with [FECHA], [orderNo].

    1. If you don't want to see the [orderNo] column, you can make it white by clicking Field formatting

    2. Place its field in the last column and hide it in the drag window.

    3. Or click power query, Add Column – Index Column – From 1. Use the [Index] column instead.

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      This solution is not acceptable because I need it as measures to render in a chart. The final result should be a bar chart, not a table. So it isn't an acceptable solution to hide a column in a table.