cancel
Showing results for
Did you mean:

# Calculate amounts by order MFelix
Super User
1322 Views Super User

## Calculate amounts by order

This come up from a post on the community, were it was needed to calculate the total price for orders based on a quantity / price table were the order of the table was by price:

 Amount Price 100 €1,00 300 €2,00 50 €3,00 500 €4,00 8000 €5,00 900 €6,00 2700 €7,00 4700 €8,00 6000 €9,00

The idea was if someone asked for 2000 units the order was fulfilled based on the lowest price until reaching the 2000 units so would be something like:

100 stock for    1 € =   100 €

300 stock for    2 € =   600 €

50 stock for      3 € =   150 €

500 stock for    4 € = 2.000 €

1050 stock for  5 €=  5.250 €

Total =                       8.100 €

I added a Index column to the table and then created the following measure:

```Total Values (all categories) =
//Temporary Table to calculate the cumulative values until reaching the order quantities
VAR Temporary_Table =
ALL ( Price_Quantity_Table[Price] );
"Cumulatives"; CALCULATE ( SUM ( Price_Quantity_Table[Amount] ); Price_Quantity_Table[Price] <= EARLIER ( Price_Quantity_Table[Price] ) );
"Index_N"; LOOKUPVALUE ( Price_Quantity_Table[Index]; Price_Quantity_Table[Price]; Price_Quantity_Table[Price] );
"Quantity"; LOOKUPVALUE ( Price_Quantity_Table[Amount]; Price_Quantity_Table[Price]; Price_Quantity_Table[Price] )
)

//Quantity selection of the order based on cumulatives values
VAR Quantity_Selection =
CALCULATE (
MAX ( Price_Quantity_Table[Amount] );
FILTER ( Temporary_Table; [Cumulatives] <= Parameter[Parameter Value] )
)

//Index column for the quantity selection
VAR Index_Selection =
CALCULATE (
MAX ( Price_Quantity_Table[Index] );
FILTER ( Temporary_Table; [Cumulatives] <= Parameter[Parameter Value] )
)

//First Quantity above the desired quantity to be used for the price of the last part of the fullfilment
VAR Quantity_Selection_Above =
CALCULATE (
MAX ( Price_Quantity_Table[Amount] );
FILTER ( Temporary_Table; [Index_N] = Index_Selection + 1 )
)

//Cumulative quantity to calculate the difference between quantity of order and the last price
VAR Quantity_Cumulative =
CALCULATE (
SUMX ( Price_Quantity_Table; Price_Quantity_Table[Amount] );
FILTER ( Price_Quantity_Table; Price_Quantity_Table[Index] <= Index_Selection )
)

//Price for the last quantity to be fulfill
VAR Price_Selection_Above =
CALCULATE (
MAX ( Price_Quantity_Table[Price] );
FILTER ( Temporary_Table; [Index_N] = Index_Selection + 1 )
)
RETURN
CALCULATE (
SUMX ( Price_Quantity_Table; Price_Quantity_Table[Amount] * Price_Quantity_Table[Price] );
FILTER ( Temporary_Table; [Cumulatives] <= Parameter[Parameter Value] )
) + ( ( Parameter[Parameter Value] - Quantity_Cumulative ) * Price_Selection_Above )``` If someone has other ideas on how to do this please share.

Regards,

MFelix