I am having a challenge here to calculate the cases (based on date, month or productID), here is what I need to achieve. On the transaction table, we have ordered units for each product and we also have an attribute of Units per case for each product. The number of cases will be case numbers (total units/units per case) for each product and then sum them up.
I think you just need a SUMX formula if i understand the problem correctly? Please look at attached file and Number of cases shipped v1.pbix (27.0 KB)
let me know.
I should have been more clear on what I need. The number of cases is calculated as following:
Total units ordered / units per case for each product. For example, if one order has 12 units of product A and 16 units of product B. If product A is 12 units per case and product B is 6 units per case, then the number of case for product A will be 1 and number of case for product B is 16/6 = 2.67. so for this order, the total number of case is 3.67. However, if one product could appear in multiple orders, then
the calculation should be first to calculating the number of case for each product, and then sum them up.
So I change your formula to the following
Total Cases = SUMX(‘Table’,[Total Units Ordered]/‘Table’[Unit per case]). It works for one scenario. But I am not sure if it calculated at the product level and then sum the number of case up. How I can validate it?
Hi Mike,
I have tested with 2 different formula’s and the both give the same result. Looks ok to me.
Preference is to use DIVIDE function too, I have used that in the attached file.
Another thing to be mindful of is summarisation of columns, I have set this to no summarisation.
Hi @Mike, did the response provided by @KimC help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.
I hope that you are having a great experience using the Support Forum so far. Kindly take time to answer the Enterprise DNA Forum User Experience Survey, we hope you’ll give your insights on how we can further improve the Support forum. Thanks!
