Calculate amounts by order

MFelix — Mon, 06 May 2019 23:07:37 GMT

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 =
    ADDCOLUMNS (
        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

eyJrIjoiZDBlMmZlZDItY2QyNy00MzlkLTgxODEtODc1NDc2MDE2YjBlIiwidCI6IjczNGQyYTI3LThjODktNDMyOC1iZTYwLTRhMWQxMWNkNWM3OCIsImMiOjl9

topic Calculate amounts by order in Quick Measures Gallery

Calculate amounts by order