Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Ronald123
Resolver III
Resolver III

Calculate order amount by amount orderbook

Hello,

 

Can someone help me with a measure what gives on every row in de the second matrix the total order ammount?

 

Matrix one is the order book with the availbles amount with prices.

In the text colum an overvieuw of the expected results.


Thx,

 

Ronald

 

Naamloos.png

1 ACCEPTED SOLUTION

Hi @Ronald123 ,

 

Sorry didn't understood that the value was your order on the table.

 

Add an index column to your price table starting in 1 then add the following measure to replace the previous one:

Total Values (all categories) =
VAR Temporary_Table =
    ADDCOLUMNS (
        ALL ( Table1[Price] );
        "Cumulatives"; CALCULATE ( SUM ( Table1[Amount] ); Table1[Price] <= EARLIER ( Table1[Price] ) );
        "Index_N"; LOOKUPVALUE ( Table1[Index]; Table1[Price]; Table1[Price] );
        "Quantity"; LOOKUPVALUE ( Table1[Amount]; Table1[Price]; Table1[Price] )
    )
VAR Quantity_Selection =
    CALCULATE (
        MAX ( Table1[Amount] );
        FILTER ( Temporary_Table; [Cumulatives] <= Parameter[Parameter Value] )
    )
VAR Index_Selection =
    CALCULATE (
        MAX ( Table1[Index] );
        FILTER ( Temporary_Table; [Cumulatives] <= Parameter[Parameter Value] )
    )
VAR Quantity_Selection_Above =
    CALCULATE (
        MAX ( Table1[Amount] );
        FILTER ( Temporary_Table; [Index_N] = Index_Selection + 1 )
    )
VAR Quantity_Cumulative =
    CALCULATE (
        SUMX ( Table1; Table1[Amount] );
        FILTER ( Table1; Table1[Index] <= Index_Selection )
    )
VAR Price_Selection_Above =
    CALCULATE (
        MAX ( Table1[Price] );
        FILTER ( Temporary_Table; [Index_N] = Index_Selection + 1 )
    )
RETURN
    CALCULATE (
        SUMX ( Table1; Table1[Amount] * Table1[Price] );
        FILTER ( Temporary_Table; [Cumulatives] <= Parameter[Parameter Value] )
    ) + ( ( Parameter[Parameter Value] - Quantity_Cumulative ) * Price_Selection_Above )

On my tests it work correctly but please try with more value just to be sure.

 

Regards,

MFelix

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

8 REPLIES 8
MFelix
Super User
Super User

Hi @Ronald123 ,

 

Don't know if I understand your logic correctly but it appears to me that you have two different ways of calculating the values.

 

When you pick the 100 you get the price 0,3 and for 150 you pick up until 100 the 0,3 then the value above,this is clear to me. However when you go to the 500 although you have that price in the list you get the 100, 300 and then 500 how is this split why on some calculation you are picking up the value in the list and the remaining from the above and some times you are getting all the amount below. On your list there is also 50 and 70 so would make sense that the 100 would be the 50 at 0,33 and 50 at 0,36.

 

I have made one measure for picking up the minimum quantity and then the remaining from the next category.

 

Total Values (category and above) = 
VAR Quantity_Selection =
    CALCULATE (
        MAX ( Table1[Amount] );
        FILTER ( Table1; Table1[Amount] <= Parameter[Parameter Value] )
    )
VAR Quantity_Selection_Above =
    CALCULATE (
        MIN ( Table1[Amount] );
        FILTER ( Table1; Table1[Amount] > Parameter[Parameter Value] )
    )
VAR Price_Selection =
    LOOKUPVALUE ( Table1[Price]; Table1[Amount]; Quantity_Selection )
VAR Price_Selection_Above =
    LOOKUPVALUE ( Table1[Price]; Table1[Amount]; Quantity_Selection_Above )
RETURN
    Quantity_Selection * Price_Selection
        + IF (
            [Parameter Value] - Quantity_Selection > 0;
            ( [Parameter Value] - Quantity_Selection ) * Price_Selection_Above
        )

Change [Parameter Value] by the value you will select the quantity for purchase.

 

Regards,

MFelix

 

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Hello @MFelix ,

 

Thanks for you're reply.

 

It's correct that the example of a purchase with the amount 500 is incorrect.

The calculation rule is start with the MIN price. If the avaible ammount is less than the total purchse, go to the second price, again and again, this must be stop when the total pruchase order is filled.

 

Table 1 is an example of an order book from an exchange.

 

Person A will buy a total amout to buy 500

 

Expected Results
100 0.30 = 30

300 0.31 = 93

50 0.33 = 16.50

50 0.34 = 17

 

Total 156,50

 

Person A will buy a total amout off 1000

 

Expected Results

100 0.30 = 30

300 0.31 = 93

50 0.33 = 16.50

500 0.34 = 170

50 0.35 = 17.50

 

Total 327

Hi @Ronald123 ,

 

Although there is still a small difference on your information the last 50 units are based on the 0,38 and not0,35 as you refer and on this example you don't consider the 70 units, I have come up with this measure:

 

Total Values (all categories) =
VAR Temporary_Table =
    ADDCOLUMNS (
        ALL ( Table1[Amount] );
        "Cumulatives"; CALCULATE (
            SUM ( Table1[Amount] );
            Table1[Amount] <= EARLIER ( Table1[Amount] )
        )
    )
VAR Quantity_Selection =
    CALCULATE (
        MAX ( Table1[Amount] );
        FILTER ( Temporary_Table; [Cumulatives] <= Parameter[Parameter Value] )
    )
VAR Quantity_Selection_Above =
    CALCULATE (
        MIN ( Table1[Amount] );
        FILTER ( Temporary_Table; [Cumulatives] > Parameter[Parameter Value] )
    )
VAR Quantity_Cumulative =
    CALCULATE (
        SUMX ( Table1; Table1[Amount] );
        FILTER ( Table1; Table1[Amount] <= Quantity_Selection )
    )
VAR Price_Selection_Above =
    CALCULATE (
        MIN ( Table1[Price] );
        FILTER ( Temporary_Table; Table1[Amount] = Quantity_Selection_Above )
    )
RETURN
    CALCULATE (
        SUMX ( Table1; Table1[Amount] * Table1[Price] );
        FILTER ( Temporary_Table; [Cumulatives] <= Parameter[Parameter Value] )
    ) + ( Parameter[Parameter Value] - Quantity_Cumulative ) * Price_Selection_Above

 

Check the PBIX file attach with the simulated values.

 

I have also left the calculation for the current value and category above that I have place on previous post.

 

Regards,

MFelix

 

Regards,

MFelix


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Helle @MFelix ,

 

Thx for the quick reply.

Please take a look at the PBIX file in the link.

https://1drv.ms/u/s!An5X7wdH9O9ei3Q4notHUI1bBOcT

Greetz,

Ronald

Hi @Ronald123 ,

 

Once again sorry for refering this but believe that you have two rules:

 

Parameter 100:

You have 100 * 1 € but in fact you have a level of 50 so you would get:

50 * 3 € = 150 €

(100-50) * 1€ = 50 €

Total = 200 €

 

On the 2000 you are skiping the 900  units line so values are:

Redone Calculations

You are missing the 900 pieces line

50 * 3 € = 150 €

100 * 1 € = 100 €

300 * 2 € = 600 €

500 * 4 €  = 2.000 €

900 * 6 € 5 = 5.400 €

150  * 7 € = 1.050 €

Total = 9.300 €

 

Can you please check or tell me if you have any other rules to skip some line on the prices?

 

Regards,

MFelix


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



@MFelix,

I will use the calculation to calculate how many it will cost to buy X ammount stocks.

The calculation is on base of an order book with the actual availble supply.
In the example for buying 100 stocks, why would anyone paid € 200 if the orderbook has an
availble supply of 100 stocks for the price of €1,-?

If there is no more supply on the first line on the orderbook with the lowest price, go to the second lind with the second lowest price. Not filter on amount but price.

Thanks for you're help, i hope you can change the measure.

Greets,

Ronald


Parameter 100:

You have 100 * 1 € but in fact you have a level of 50 so you would get:

50 * 3 € = 150 €

(100-50) * 1€ = 50 €

Total = 200 €

Hi @Ronald123 ,

 

Sorry didn't understood that the value was your order on the table.

 

Add an index column to your price table starting in 1 then add the following measure to replace the previous one:

Total Values (all categories) =
VAR Temporary_Table =
    ADDCOLUMNS (
        ALL ( Table1[Price] );
        "Cumulatives"; CALCULATE ( SUM ( Table1[Amount] ); Table1[Price] <= EARLIER ( Table1[Price] ) );
        "Index_N"; LOOKUPVALUE ( Table1[Index]; Table1[Price]; Table1[Price] );
        "Quantity"; LOOKUPVALUE ( Table1[Amount]; Table1[Price]; Table1[Price] )
    )
VAR Quantity_Selection =
    CALCULATE (
        MAX ( Table1[Amount] );
        FILTER ( Temporary_Table; [Cumulatives] <= Parameter[Parameter Value] )
    )
VAR Index_Selection =
    CALCULATE (
        MAX ( Table1[Index] );
        FILTER ( Temporary_Table; [Cumulatives] <= Parameter[Parameter Value] )
    )
VAR Quantity_Selection_Above =
    CALCULATE (
        MAX ( Table1[Amount] );
        FILTER ( Temporary_Table; [Index_N] = Index_Selection + 1 )
    )
VAR Quantity_Cumulative =
    CALCULATE (
        SUMX ( Table1; Table1[Amount] );
        FILTER ( Table1; Table1[Index] <= Index_Selection )
    )
VAR Price_Selection_Above =
    CALCULATE (
        MAX ( Table1[Price] );
        FILTER ( Temporary_Table; [Index_N] = Index_Selection + 1 )
    )
RETURN
    CALCULATE (
        SUMX ( Table1; Table1[Amount] * Table1[Price] );
        FILTER ( Temporary_Table; [Cumulatives] <= Parameter[Parameter Value] )
    ) + ( ( Parameter[Parameter Value] - Quantity_Cumulative ) * Price_Selection_Above )

On my tests it work correctly but please try with more value just to be sure.

 

Regards,

MFelix

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



@MFelix ,

 

Great solution, thanks for all the help en patience!

 

Greets,

 

Ronald

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.