Transforming value to product case quantity

DAX DAX Calculations
1

I tried reviewing open forum topics and did not find any that are relevant. If there is an existing post that covers this please refer me to it
I work in purchasing and I am fairly new to using Power BI. Since I am so new, I have found it simpler to write several measures rather than one large measure. I am working on a project that calculates a company term called " Open to Buy" and “To Purchase” I getting to this by writing the following measures:

Demand QTY=CALCULATE(SUM(‘Demand Forecast’[Sales Forecast]))`

Cumulative Demand =CALCULATE( [Demand QTY], FILTER(ALLSELECTED(‘Date Table’), ‘Date Table’[Date] <=MAX(‘Date Table’[Date])))

Open to Buy =CALCULATE([Cumulative Demand]-‘Inventory Position’[Sum of Qty on Hand (Primary UOM)]-‘Inventory Position’[Sum of Qty on Open PO (Primary UOM)])

All fairly simple so far. However, this is returning values that I can not purchase on. I have to purchase products in case quantities and I have to purchase many products at once all with potentially different case quantities. I have an item table as a look up table that is related to my sales, demand, and inventory tables. My item table has my case quantity values for all of my items

The next step i tried is below, but it is not working and i am not sure what to do next…My end goal is to transform my open to buy value to an even case quantity that is the next lower increment.
Example: Open to buy value = 17 , case qty for that product is 5, open to buy value of 17 is transformed to a “to purchase” value of 15.

To purchase =IF([Open to Buy]<=0,0, FLOOR([Open to Buy], (‘Item Info’[CASE QTY (PUOM)])))

Thanks in advance for any assistance.

2

Hi @ZacH,

Welcome to the forum!

Based on the logic you’ve described try to use INT instead to calculate a ‘to purchase’ quantity:
INT( DIVIDE( [Open to Buy], ‘Item Info’[CASE QTY (PUOM)] )) * ‘Item Info’[CASE QTY (PUOM)]

the above should result in 15. BTW is the CASE QTY a measure, variable, or … ??
If you need any further assistance please provide a sample PBIX.

I hope this is helpful.

3

B1 Spreadsheet.xlsx (3.5 MB)

Thanks for welcoming me to the forum and your reply. The case QTY is simply a value in my item info table. THe solution you provided did not work…file attached. My coworkers are more comfortable in excel so i am using power query and power pivot…

4

Thanks for posting your question @ZacH. To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.

Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

Including all of the above will likely enable a quick solution to your question.

5

Hi @ZacH,

Right, not at all familiair with Power Pivot but hey… I’ll give it a shot, saw that ‘Item Info’[CASE QTY (PUOM)] is a table[column] reference so in Power BI we’d wrap SELECTEDVALUE around it but that doesn’t seem to be available in Power Pivot. Tried VALUES that didn’t return a scalar value although I checked and all your Item numbers are unique. So three time’s charm right and I went for TOPN

TOPN( 1, VALUES('Item Info'[CASE QTY (PUOM)]), [CASE QTY (PUOM)], ASC )

So write that inside your logic and see if it does the trick - let me know.

1 Like
6

Melissa - Thanks for the support. This returned my case quantities. I then used this measure in an additional FLOOR measure and this provided the end result I was seeking!

1 Like