Forum Discussion

pallavinegi's avatar
pallavinegi
Frequent Visitor
3 years ago
Solved

Minimizing the formula.

Hi,

I am new to Dax, have a formula but don't know how to minimize it-

Current Month Average =
IF (
'Table'[Tracking Parameters] = "PO Receipt to SO Creation",
AVERAGE(Sheet1[PO Receipt to SO Creation]
),
IF('Table'[Tracking Parameters] = "SO Creation to SO Release",
AVERAGE(Raw[SO Creation to SO Release]
),
IF('Table'[Tracking Parameters] = "SO Release to SO Planning",
AVERAGE('TAT Report'[SO Release to SO Planning]
),
IF('Table'[Tracking Parameters] = "Planning to Vehicle Reporting",
AVERAGE('TAT Report'[Planning to Vehicle Reporting]),
IF('Table'[Tracking Parameters] = "Vehicle Reporting to Vehicle GateIn",
AVERAGE('TAT Report'[Vehicle Reporting to Vehicle Gate In]),
IF( 'Table'[Tracking Parameters] = "Vehicle Gate In to Vehicle Gate Out",
AVERAGE('TAT Report'[Vehicle Gate In to Gate Out]),
IF( 'Table'[Tracking Parameters] = "Dispatch To Delivery",
AVERAGE('TAT Report'[TAT 9 Transit Time (Dispatch to Delivery)(Hours)]),
0)

)
)
)
)
)

  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    Please try something like below whether it suits your requirement.

     

    Current Month Average =
SWITCH (
    'Table'[Tracking Parameters],
    "PO Receipt to SO Creation", AVERAGE ( Sheet1[PO Receipt to SO Creation] ),
    "SO Creation to SO Release", AVERAGE ( Raw[SO Creation to SO Release] ),
    "SO Release to SO Planning", AVERAGE ( 'TAT Report'[SO Release to SO Planning] ),
    "Planning to Vehicle Reporting", AVERAGE ( 'TAT Report'[Planning to Vehicle Reporting] ),
    "Vehicle Reporting to Vehicle GateIn", AVERAGE ( 'TAT Report'[Vehicle Reporting to Vehicle Gate In] ),
    "Vehicle Gate In to Vehicle Gate Out", AVERAGE ( 'TAT Report'[Vehicle Gate In to Gate Out] ),
    "Dispatch To Delivery", AVERAGE ( 'TAT Report'[TAT 9 Transit Time (Dispatch to Delivery)(Hours)] ),
    0
)

1 Reply

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

    Hi,

    Please try something like below whether it suits your requirement.

     

    Current Month Average =
SWITCH (
    'Table'[Tracking Parameters],
    "PO Receipt to SO Creation", AVERAGE ( Sheet1[PO Receipt to SO Creation] ),
    "SO Creation to SO Release", AVERAGE ( Raw[SO Creation to SO Release] ),
    "SO Release to SO Planning", AVERAGE ( 'TAT Report'[SO Release to SO Planning] ),
    "Planning to Vehicle Reporting", AVERAGE ( 'TAT Report'[Planning to Vehicle Reporting] ),
    "Vehicle Reporting to Vehicle GateIn", AVERAGE ( 'TAT Report'[Vehicle Reporting to Vehicle Gate In] ),
    "Vehicle Gate In to Vehicle Gate Out", AVERAGE ( 'TAT Report'[Vehicle Gate In to Gate Out] ),
    "Dispatch To Delivery", AVERAGE ( 'TAT Report'[TAT 9 Transit Time (Dispatch to Delivery)(Hours)] ),
    0
)