Dynamically calculating cost based on user input quantity

[Rajesh1]

Good day,

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

Zone	Product	Unit Cost	Fixed service charges	Quantity From	Quantity To	Variable Charges per unit
USA	P1	350	15	1	80	20
USA	P1	350	15	81	90	15
USA	P1	350	15	91	99999	5

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 formula	Fixed service charge	Variable charge	Total cost	Unit cost formula	Unit cost
350 x 70	15	20 x 70	25,915	25,915 / 70	370.21

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

Basic cost formula	Fixed service charge	Variable charge	Total cost	Unit cost formula	Unit cost
350 x 90	15	15 x 90	32,865	32,865 / 90	365.17

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

Basic cost formula	Fixed service charge	Variable charge	Total cost	Unit cost formula	Unit cost
350 x 100	15	5 x 100	35,515	35,515 / 100	355.15

Please can someone assist with this.

Thanks,

Rajesh 

[Zubair_Muhammad · Accepted solution]

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

[Zubair_Muhammad · Accepted solution]

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 · Accepted solution]

Thanks very much for your timely assistance, @Zubair_Muhammad!

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