cancel
Showing results for 
Search instead for 
Did you mean: 

Calculate amounts by order

Super User
1322 Views
Super User
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 =
    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 )

Orders_quantiy.png

 

 

If someone has other ideas on how to do this please share.

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!