Forum Discussion
Complex round up sumx issue
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.
9 Replies
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.
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?
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]))
- 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
Dmy 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...
