Round down to case Quantities

[Anonymous]

Since I am new to Dax I am building out my needs step by step. In a previous measure I calculate a value my company terms "Open to Buy" Which considers inventory on hand, open purchases, and forecast quantities. 

In my Item info table I have a column of values which lists the Cases quantity for each product. The case quantity is the multiple the products need to be purchased by. However, I am not having any luck getting this to work. Any help is greatly appreciated.

=FLOOR([Open to Buy], 'Item Info'[CASE QTY (PUOM)])

I recieve the following message:

"Calculation error in measure 'Key Measures'[Open to Buy + Pack size]: A single value for column 'CASE QTY (PUOM)' in table 'Item Info' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

[mahoneypat]

If your Case Qty and Open values were in the same table, your measure would likely work as a calculated column.  However, you need to aggregate one or both of them to end up with a scalar value for each to go into the FLOOR() functions.  Something like this:

FloorMeasure = var selectedproduct = selectedvalue(Table[Product]) // assume this measure is in a visual that has the product field so there is only one product for each calculation
var opentobuy = [Open To Buy] //assumes this is an existing measure

var caseqty = Calculate(Min('Item Info'[CASE QTY (PUOM)], 'Item Info'[Product]=selectedproduct) // may be easier way to get this depending on model but you get the idea

return FLOOR(opentobuy, caseqty)

If this solution works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.
Regards,
Pat

[amitchandak]

@Anonymous , the formula is not right. What is usage of floor here

[Anonymous]

@amitchandak 

The usage of floor was intended to round my open order quanities down to even case quantities.

for example if I have a case quantity of 5 with with an open to buy of 7, I want to round my open to buy value to 5.

Another example would be a product with a case quantity of 4 with an open to buy of 14, I want to round my value down to 12. 

[camargos88]

Hi @Anonymous ,

Check this link: https://docs.microsoft.com/pt-br/dax/floor-function-dax

Also, measures work with aggregators (sum, max, min, avg...)