DAX aggregation help

jake32008

New Member
Joined
Apr 10, 2015
Messages
27
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

GROUPINGCALCULATIONEXAMPLEQUANTITY
PRODUCT GROUPMINPRODUCT GROUP A300
PROCESSSUMPROCESS 1300
MACHINEMINMachine 1300
PRODUCT TYPESUM(blank)300
PRODUCTION SHIFT (blank)300
PROCESSSUMPROCESS 2450
MACHINEMINMachine 1175
PRODUCT TYPESUM(blank)175
PRODUCTION SHIFT First50
PRODUCTION SHIFT Second75
PRODUCTION SHIFT Third50
MACHINEMINMachine 2275
PRODUCT TYPESUM(blank)275
PRODUCTION SHIFT First100
PRODUCTION SHIFT Second50
PRODUCTION SHIFT Third125
PROCESSSUMPROCESS 3500
MACHINEMINMACHINE 1250
PRODUCT TYPESUMType A250
PRODUCTION SHIFT (blank)250
MACHINEMINMACHINE 2250
PRODUCT TYPESUMType A350
PRODUCTION SHIFT (blank)350
PRODUCT TYPESUMType B250
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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Forum statistics

Threads
1,214,605
Messages
6,120,473
Members
448,967
Latest member
visheshkotha

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top