Hi All,
I am using power pivot for some item sales and inventory analysis. I need the formula for the following scenario which I'll try to explain clearly as I do not have any non sensitive data that I can upload at the moment and the deadline is fast approaching for when I need to have this project finished 🙁
What I need to do is calculate the suggested order qty using the following criteria:
If "Actual Mths SOH" (a measure col in pivot) is less than "Req'd Mths SOH" (column in a table) then subtract "Actual Mths SOH" from "Req'd Mths SOH" and multiply the result by "Mths ROS" (a measure col in pivot) this then gives the suggested qty to be ordered to get the stock back to the "Req'd Mths SOH" qty (ie say 6 months supply)
I hope I have managed to explain clearly enough so someone will be able to point me in the right direction! I have looked at using SUM, IF and Calculate so far with no success at all
Thanks
Gayle
Hi Gayle,
I recommend you write explicit measures for each component you need to calculate. You can then check the results for each component are as expected (in the PivotTable) and then reference these explicit measures in your final formula. At the moment you don't have an explicit measure for the Req'd Mths SOH.
Mynda
