Complex round up sumx issue

[zenisekd]

Hi folks, I am facing a problem that I have spent hours on and still can't crack it. 

Here is my situation:

I am selling various products (A,B,...), each of kind of product is stored in my warehouse on a pallet in a specific amount (100 pcs of product A fits a 1 pallet, while 200 pcs of B fits on the same pallet). 
As a warehouse manager, I would be interested in how many pallets disappears everyday, based on my sales. (I would like to know how many pallet slots will vacant every day). This represents the main issue. If I sell on Monday 0,7 of A pallet, the number of vacant pallets is 0. Once I sell on Tuesday 0,5 pallet of A again, The number I am looking for will be 1 (1,2 rounded down to 1 and 0,2 is left for the following days) and if I sold on Wednesday 0,9 pallet, then the number is 1 (and 0,1 is left for the next withdrawal to round up)...

Bellow, I enclose an example of tables. Yellow are default tables, White is wished result (grey is a bonus...). Can anyone help me out solving this? Thanks.

[Anonymous]

Please inspect the attached file. All the other measures you want are easily created from the ones already int there.

Best

D

[Anonymous]

I think you've got a mistake in the description.

"f I sell on Monday 0,7 of A pallet, the number of vacant pallets is 0. Once I sell on Tuesday 0,5 pallet of A again, The number I am looking for will be 1 (1,2 rounded down to 1 and 0,2 is left for the following days [NOTE: .8 is left, not .2) and if I sold on Wednesday 0,9 pallet, then the number is 1"

I think the number is 2 (vacant pallets) and there is 0.9 left of the partial one for a future withdrawal. That's because what you are doing is you're trying to calculate the number of empty pallets within a period of time, not only on a single day, so you have to accumulate the amounts properly.

Best
D

[zenisekd]

@Anonymous 

my aim is to create a line chart, which will tell me, how many pallets have been emptied each day. For this reason I need highlighted information: 
Monday - 0,7 pallet A sold= 0 pallet empty   - 0,3 left on the first pallet
Tuesday - 0,5 pallet A sold = 1 pallet emptied  - 0,8 left on the second pallet pallet
Wednesday - 0,9 pallet A sold = 1 pallet emptied - 0,9 left on the third pallet

However I am also intrested in total:

Total emptied pallets: 2 


I am afraid that your formulas doesn't work...

[Anonymous · Accepted solution]

This one works the way you wanted... See the file attached. But don't ask me about the formula. It's a bit of math trickery.

Best

D

[MattAllington]

This looks like a life time to date running total rounded down to full pallets. I don't think it is a sumx problem. First create a calendar table and join it

https://exceleratorbi.com.au/power-pivot-calendar-tables/

then create a matrix with date from the calendar table and create a slicer so you can work on one product at a time. Start with a filter on prod A. 

add 2 measures

pcs sold measure = sum(sales[pc sold])

lifetime to date = calculate([pcs sold measure],filter(all(calendar[date]), calendar[date]<= max(calendar[date])))

this will give you a running total. Now you just need to chunk it into pallets. I assume something like

pallets shipped = INT([lifetime to date],selectedvalue(product[pcs on pallet]))

ok, if you want the products to be additive by pallet, you will need sumx, something like 

total pallets =sumx(products,INT([lifetime to date],product[pcs on pallet]))

I'm on an iPad, so it is hard to test it, but I think this will be close. 

[zenisekd]

Thanks, Matt,

I have figured, that the last step was supposed to be  
total pallets =sumx(products,INT([lifetime to date]/product[pcs on pallet]))
instead of 
total pallets =sumx(products,INT([lifetime to date],product[pcs on pallet]))

and at first glance it seems to be working fine. 

however, the third formula doesn't seeem to be working and I can´t quite figure out why... I thought it might be again 
pallets shipped = INT([lifetime to date]/selectedvalue(product[pcs on pallet])),
but it throws me an error, when I try to use it in the matrix, saying that the result is either too small or too big...

Any advice?

[MattAllington]

yes, sorry about the comma.  I originally wrote a MOD function, then changed it to INT but forgot to change the comma to a divide.  I think the last formula is correct as is.  You do not need selectedvalue because it is inside a SUMX over the products table.

total pallets =sumx(products,INT([lifetime to date]/product[pcs on pallet]))

[zenisekd]

@MattAllington 

The total pallets formula works well, but the formula "pallets shipped", which is important to me is not working... it shows the measure works, but once I place it to the matrix, it gives an error. 

[MattAllington]

The only measure that will work in an additive way is the SUMX version, the other one will only work if there is a single product.  But you say it works, then you say it returns an error, so I am not really sure what that means.