OTIF Calculate for Logistics

[hudsoncr]

Hello everyone,

I got a headache trying to figure out how to properly show the results in the images (scratches) below.

I watched some videos and lectures however I can't solve it by myself. Even the quantity of orders by CustomerID wasn't extracted properly. It always showed the total.

In the last table below there are the metrics that I need and the tables where to extract the measure.

Tables in CSV in link:   Google Drive 

I really appreciate it if someone could help me with how to write these measures and be dynamic.

I hope that I explained well.

- There are the same customers in different cities, so when the cities ain't filtered (not selected a specific one), show the average.

Values 1 - Attend

Values 0 - Not Attend

[hudsoncr · Accepted solution]

It worked now.

1) I didn't notice that the measures should be under the Customer table. I put and voilà.

2) I did some adjustments to some formulas to work properly in the Matrix visual. Thanks for the help @v-zhangti .

IF%

IF% = DIVIDE(
CALCULATE(COUNT(fact_orders_aggregate[order_id]),
FILTER(fact_orders_aggregate,[in_full]=1)),[Total_Orders]
)

The colour of the background change depending on target vs performance.

Color_IF = 
var V=DIVIDE(dim_customers[IF%],dim_targets_orders[IF_Target%])
return IF(V>=1,"#FFFFFF",
            if(V>0.7,"#01B8AA",
                IF(V>0.3&&V<0.7,"#F2C80F",
                    IF(V>0,"#FD625E"))))

 

Now, the next headache is doing like the drawing below. By periods.

I'm trying but the values don't spread through the period properly.

[v-zhangti]

Hi, @hudsoncr 

You can try the following methods.
Measure:

Total Order Lines = COUNT(fact_order_lines[order_id])

Line Fill Rate = 
Var _infull=CALCULATE(COUNT(fact_order_lines[order_id]),FILTER(ALL(fact_order_lines),[In Full]=1))
Var _all=COUNT(fact_order_lines[order_id])
Return
DIVIDE(_infull,_all)

Volume Fill Rate = 
Var _ship=CALCULATE(DISTINCTCOUNT(fact_order_lines[order_id]),FILTER(ALL(fact_order_lines),[In Full]=1))
Var _Order=DISTINCTCOUNT(fact_order_lines[order_id])
Return
DIVIDE(_ship,_Order)

Total Orders = COUNT(fact_orders_aggregate[order_id])

On Time Delivery % = DIVIDE(CALCULATE(COUNT(fact_orders_aggregate[order_id]),FILTER(ALL(fact_orders_aggregate),[on_time]=1)),[Total Orders])

In Full Delivery % = DIVIDE(CALCULATE(COUNT(fact_orders_aggregate[order_id]),FILTER(ALL(fact_orders_aggregate),[in_full]=1)),[Total Orders])

On Time In Full % = DIVIDE(CALCULATE(COUNT(fact_orders_aggregate[order_id]),FILTER(ALL(fact_orders_aggregate),[otif]=1)),[Total Orders])

On Time Target = CALCULATE(AVERAGE(dim_targets_orders[ontime_target%]))

In Full Target = CALCULATE(AVERAGE(dim_targets_orders[infull_target%]))

On Time In Full Target = CALCULATE(AVERAGE(dim_targets_orders[otif_target%]))

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[hudsoncr]

Hello @v-zhangti ,

That's the way, but I still need it dynamically.

As you see below, I put the data in a table and it doesn't change. I'd need it by period as well.

By the way, how did you attach the PBIX file to the reply?