Dividing Total Kgs per Process Order according to its Nominal Speed
Here's my case, it is a little bit dense but I'll try to explain it as plainly as possible.
I would like to calculate a variable called GoodProdTime. According to my theoretical formula, GoodProdTime = Kg of good production / Nominal Speed. The problem that I am facing is that this calculus should be done independently per each process order (PO), as each process order will have a different amout of kgs of good production but also a different nominal speed. To put it in a formula this is what I would like to do (according to a filter of time that I'm applying):
As of now, what I'm able to calculate is the following one:
The way I calculate this second formula is the following one:
As you can see what I'm doing is calculating the summatory of kgs (QuantityFP) according to some filters from its table and dividing it by the average of nominal speeds according to some filters as well.
There is a variable that links the two tables, linking the idPO from the table 'ng SC-EGR-EGR-BAT' with the idPO from the table 'ng PoMixParameter', which is the table that stores the nominal speed per each OP.
I've tried to explain it as well as possible, if any of you have any doubts please let me know and I'll be happy to answer them.
Is there anyone that could help me out with this formula? Any help would be greatly appreciated!
@GuerauFF I love the operations stuff! Could you please post sample data and expected output. I know it's kind of a pain but it makes the resolution time 10x faster and 100% more accurate. Please @ me in response. I love these kinds of problems! Better yet, the PBIX file posted as a shared link.
Are you getting an error with the formula or just not the right results?
Love your enthusiasm with this use-case! The formula is working fine, it's the result that is not coming out right.
Sadly due to compliance issues with my company I'm not allowed to share the original data set but I've created a simpler version that depicts the same issue so you can see what I mean.
I attach both the pbix file and the excel data set (with the expected result as you said). I think I granted you access correctly, if not let me know and I'll do it again (I could send them to you via e-mail if needed).
I have one doubt still though... How would you apply the filters that I mentioned? Both the quantity and the nominal speed should be filtered according to certain specifications on some of the other columns of their table.
The original data set is quite more complex than this example and I'm finding it difficult to apply your solution.
Really appreciate your help though, would be awesome if we could get this done!
@GuerauFF Must have missed the part about filters, will need you to explain that to be please. I have noted where you could apply specific filters although nothing in this measure breaks filter context so you should be able to use it just about anywhere:
VAR __Table =
'Hoja1', //You could use FILTER('Hoja1',<filters go here>)
SUMX(__Table,[__GoodProdTime]) //You could apply filters here SUMX(FILTER(__Table,<filters>),[GoodProdTime])