cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Rajesh1 Frequent Visitor
Frequent Visitor

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 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Dynamically calculating cost based on user input quantity

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]
    )
)

313.png

Highlighted
Rajesh1 Frequent Visitor
Frequent Visitor

Re: Dynamically calculating cost based on user input quantity

Thanks very much for your timely assistance, @Zubair_Muhammad!

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

2 REPLIES 2
Super User
Super User

Re: Dynamically calculating cost based on user input quantity

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]
    )
)

313.png

Highlighted
Rajesh1 Frequent Visitor
Frequent Visitor

Re: Dynamically calculating cost based on user input quantity

Thanks very much for your timely assistance, @Zubair_Muhammad!

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