Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Galleries
- Quick Measures Gallery
- Calculate amounts by order

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Calculate amounts by order

05-06-2019
04:07 PM

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

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português