Measure to determine order count with filter

[Anonymous]

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:

DATE

OrderNo

SKU

QTY

QTY_ORDERED

QTY_DEILVERD

QTY_NOT_DELIVERED

CATEGORY

CPG

ROUTE

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!

[Anonymous]

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]

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.

[amitchandak]

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