Forum Discussion
Super UserComplex round up sumx issue
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
Community ChampionThis 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.
Super UserThanks, 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
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.