Forum Discussion

Rajesh1's avatar
Rajesh1
Helper I
8 years ago
Solved

Dynamically calculating cost based on user input quantity

Good day,

 

A sample of our product cost matrix per zone is as below:

ZoneProductUnit CostFixed service chargesQuantity FromQuantity ToVariable Charges per unit
USAP13501518020
USAP135015819015
USAP13501591999995

 

The requirement is, based on user input quantity, the report must choose the corresponding Variable Charges per unit and calculate the unit cost of the product.

To keep it simple, I've just presented only one zone and product in the above table.

 

Examples:

If the user input quantity is 70, the report must calculate the unit cost as below:

Basic cost formulaFixed service chargeVariable chargeTotal costUnit cost formulaUnit cost
350 x 701520 x 70       25,91525,915 / 70370.21

 

If the user input quantity is 90, the report must calculate the unit cost as below:

Basic cost formulaFixed service chargeVariable chargeTotal costUnit cost formulaUnit cost
350 x 901515 x 90       32,86532,865 / 90365.17

 

If the user input quantity is 100, the report must calculate the unit cost as:

Basic cost formulaFixed service chargeVariable chargeTotal costUnit cost formulaUnit cost
350 x 100155 x 100       35,51535,515 / 100355.15

 

Please can someone assist with this.

 

Thanks,

Rajesh 

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Hi Rajesh1

     

    Try this

     

    You can pull the Applicable Variable Charge from "Cost Matrix" to your "Input Table" using this MEASURE

     

    Applicable Variable Charge =
CALCULATE (
    VALUES ( CostMatrix[Variable Charges per unit] ),
    FILTER (
        CostMatrix,
        CostMatrix[Product] = SELECTEDVALUE ( 'InputTable'[Product] )
            && CostMatrix[Zone] = SELECTEDVALUE ( 'InputTable'[Zone] )
            && SELECTEDVALUE ( 'InputTable'[Input Qty] ) >= CostMatrix[Quantity From]
            && SELECTEDVALUE ( 'InputTable'[Input Qty] ) <= CostMatrix[Quantity To]
    )
)

  • Rajesh1's avatar
    Rajesh1
    8 years ago

    Thanks very much for your timely assistance, Zubair_Muhammad!

    Works like a charm!  thank you.. thank you.. thank you

2 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    Hi Rajesh1

     

    Try this

     

    You can pull the Applicable Variable Charge from "Cost Matrix" to your "Input Table" using this MEASURE

     

    Applicable Variable Charge =
CALCULATE (
    VALUES ( CostMatrix[Variable Charges per unit] ),
    FILTER (
        CostMatrix,
        CostMatrix[Product] = SELECTEDVALUE ( 'InputTable'[Product] )
            && CostMatrix[Zone] = SELECTEDVALUE ( 'InputTable'[Zone] )
            && SELECTEDVALUE ( 'InputTable'[Input Qty] ) >= CostMatrix[Quantity From]
            && SELECTEDVALUE ( 'InputTable'[Input Qty] ) <= CostMatrix[Quantity To]
    )
)

    • Rajesh1's avatar
      Rajesh1
      Helper I
      8 years ago

      Thanks very much for your timely assistance, Zubair_Muhammad!

      Works like a charm!  thank you.. thank you.. thank you