I am trying to calculate production capacity and have the following inputs:
Product Group - overall capacity is limited by the bottleneck process
Process
Machine
Product Type - some processes have machines have varying capacity depending on product type
Production Shift
<colgroup><col><col><col><col></colgroup><tbody>
</tbody>
I have tried using the following formula which aggregated correctly at the machine level but not at the product group level:
production capacity:=SWITCH(TRUE(),
HASONEVALUE(production_capacity[product group]), MIN(production_capacity[capacity]),
HASONEVALUE(production_capacity[process]), SUM(production_capacity[capacity]),
HASONEVALUE(production_capacity[machine]), MIN(production_capacity[capacity]),
HASONEVALUE(production_capacity[product_type]), SUM(production_capacity[capacity]),
SUM(production_capacity[capacity]))
What am I missing here? Thank you in advance for any help.
Jake
Product Group - overall capacity is limited by the bottleneck process
Process
Machine
Product Type - some processes have machines have varying capacity depending on product type
Production Shift
GROUPING | CALCULATION | EXAMPLE | QUANTITY |
PRODUCT GROUP | MIN | PRODUCT GROUP A | 300 |
PROCESS | SUM | PROCESS 1 | 300 |
MACHINE | MIN | Machine 1 | 300 |
PRODUCT TYPE | SUM | (blank) | 300 |
PRODUCTION SHIFT | (blank) | 300 | |
PROCESS | SUM | PROCESS 2 | 450 |
MACHINE | MIN | Machine 1 | 175 |
PRODUCT TYPE | SUM | (blank) | 175 |
PRODUCTION SHIFT | First | 50 | |
PRODUCTION SHIFT | Second | 75 | |
PRODUCTION SHIFT | Third | 50 | |
MACHINE | MIN | Machine 2 | 275 |
PRODUCT TYPE | SUM | (blank) | 275 |
PRODUCTION SHIFT | First | 100 | |
PRODUCTION SHIFT | Second | 50 | |
PRODUCTION SHIFT | Third | 125 | |
PROCESS | SUM | PROCESS 3 | 500 |
MACHINE | MIN | MACHINE 1 | 250 |
PRODUCT TYPE | SUM | Type A | 250 |
PRODUCTION SHIFT | (blank) | 250 | |
MACHINE | MIN | MACHINE 2 | 250 |
PRODUCT TYPE | SUM | Type A | 350 |
PRODUCTION SHIFT | (blank) | 350 | |
PRODUCT TYPE | SUM | Type B | 250 |
PRODUCTION SHIFT | (blank) | 250 |
<colgroup><col><col><col><col></colgroup><tbody>
</tbody>
I have tried using the following formula which aggregated correctly at the machine level but not at the product group level:
production capacity:=SWITCH(TRUE(),
HASONEVALUE(production_capacity[product group]), MIN(production_capacity[capacity]),
HASONEVALUE(production_capacity[process]), SUM(production_capacity[capacity]),
HASONEVALUE(production_capacity[machine]), MIN(production_capacity[capacity]),
HASONEVALUE(production_capacity[product_type]), SUM(production_capacity[capacity]),
SUM(production_capacity[capacity]))
What am I missing here? Thank you in advance for any help.
Jake