Forum Discussion
OTIF Calculate for Logistics
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
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.
3 Replies
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-zhangtiCommunity Support
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.