cancel
Showing results for
Did you mean:
Highlighted
Rajesh1 Frequent Visitor

Dynamically calculating cost based on user input quantity

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

2 ACCEPTED SOLUTIONS

Accepted Solutions 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]
)
)``` Rajesh1 Frequent Visitor

Re: Dynamically calculating cost based on user input quantity

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

2 REPLIES 2 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]
)
)``` Rajesh1 Frequent Visitor